-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathZ2_319093.sql
210 lines (187 loc) · 9.97 KB
/
Z2_319093.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
/*
Imie Nazwisko: Karol Sekściński
Numer Albumu: 319093
*/
/* proszę stworzyć skrypt w którym
1. Korzystając ze skryptu z Z1 proszę utworzyć minimum 5 baz
2. W 3ech z nich proszę utworzyć tabele i dane z wykładu z BAZ (WOJ,MIASTA,OSOBY,ETATY)
i wypełnić wartościami, jak nie mają Państwo swojego skryptu to w chmurze jest mój w
katalogu zaczynającym się od Z2... stud_zal_baza.sql
3. Proszę w jednej z baz dodać kilka rekordów więcej do ETATY i OSOBY (według uznania)
4. Zadaniem jest śledzenie liczby rekordów w tabelach w bazach
4.1 Proszę utworzyć tabele:*/
/*Tabele utworzone
CREATE TABLE APBD23_ADM.dbo.DB_CHECK
( check_id int not null IDENTITY CONSTRAINT PK_DB_CHECK PRIMARY KEY
, db_nam nvarchar(50) not null -- w jakiej bazie
, d_stamp datetime NOT NULL DEFAULT GETDATE() -- o której godzinie
, opis nvarchar(50) NOT NULL
)
CREATE TABLE APBD23_ADM.dbo.DB_CHECK_ITEMS
( check_id int not null CONSTRAINT FK_DB_CHECK__DB_CHECK_ITEMS FOREIGN KEY
REFERENCES APBD23_ADM.dbo.DB_CHECK (check_id)
, tb_nam nvarchar(50) not null -- nazwa tabeli w bazie
, tb_check_d_stamp datetime NOT NULL DEFAULT GETDATE() -- o której godzinie dodano rekord
, LICZBA_REKORDOW int NOT NULL
)
*/
/*
4.2 Trzeba utworzyć procedurę, która ma kursor po wszystkich bazach
Trzeba utworzyć procedurę, która dla podanej bazy wylistuje wszystkie tabele
wstawi rekord do tabeli APBD23_ADM.dbo.DB_CHECK i z tak uzyskanym identyfikatorem
wstawi dla kazdej tabeli aktualną liczbę rekordów do tabeli
APBD23_ADM.dbo.DB_CHECK_ITEMS
*/
CREATE PROCEDURE proc42 (@db nvarchar(50))
AS
BEGIN
DECLARE @sql nvarchar(2000), @d nvarchar(50), @i int
INSERT INTO APBD23_ADM.dbo.DB_CHECK(db_nam, opis) VALUES (@db, N'Procedura XX - ' + @db)
SET @i = SCOPE_IDENTITY()
CREATE TABLE #t (t_name nvarchar(50) NOT NULL)
SET @d = @db -- nazwa bazy ma byc parametrem procedury
SET @sql = N'USE ' + @d
+ N';INSERT INTO #t (t_name) '
+ N' SELECT o.[name] FROM sysobjects o WHERE OBJECTPROPERTY(o.[ID],N''IsUserTable'')=1'
EXEC sp_SqlExec @sql
DECLARE @t NVARCHAR(50)
DECLARE @record_count int
DECLARE CI INSENSITIVE CURSOR FOR
SELECT * FROM #t
OPEN CI
FETCH NEXT FROM CI INTO @t
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = N'SELECT @record_count = COUNT(*) FROM ' + @d + '.dbo.' + @t
EXEC sp_executesql @sql, N'@record_count int OUTPUT', @record_count OUTPUT
INSERT INTO APBD23_ADM.dbo.DB_CHECK_ITEMS(check_id, tb_nam, LICZBA_REKORDOW) VALUES (@i, @t, @record_count)
FETCH NEXT FROM CI INTO @t
END
CLOSE CI
DEALLOCATE CI
END
GO
EXEC proc42 @db = 'cc'
GO
/*
check_id db_nam d_stamp opis
----------- -------------------------------------------------- ----------------------- --------------------------------------------------
1 cc 2023-11-04 23:57:51.243 Procedura XX - cc
(1 row affected)
check_id tb_nam tb_check_d_stamp LICZBA_REKORDOW
----------- -------------------------------------------------- ----------------------- ---------------
1 woj 2023-11-04 23:57:51.307 3
1 miasta 2023-11-04 23:57:51.310 6
1 osoby 2023-11-04 23:57:51.310 10
1 firmy 2023-11-04 23:57:51.310 3
1 etaty 2023-11-04 23:57:51.310 15
(5 rows affected)
*/
/*
teraz trzeba zrobić kursor po #t i dla kazdej z tabel policzyc liczbę rekordów
i tę liczbę zapisać w tabeli APBD23_ADM.dbo.DB_CHECK_ITEMS
4.3 Napisać procedurę, która dla wszystkich baz wywoła procedurę z punktu 4.2
*/
CREATE PROCEDURE proc43
AS
BEGIN
DECLARE @d NVARCHAR(50)
DECLARE CI1 INSENSITIVE CURSOR FOR
SELECT d.[name] FROM sysdatabases d
OPEN CI1
FETCH NEXT FROM CI1 INTO @d
WHILE @@FETCH_STATUS = 0
BEGIN
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE')
BEGIN
SELECT @d AS db_name
EXEC proc42 @db = @d
END
FETCH NEXT FROM CI1 INTO @d
END
CLOSE CI1
DEALLOCATE CI1
END
EXEC proc43
SELECT * FROM APBD23_ADM.dbo.DB_CHECK_ITEMS
/*
check_id db_nam d_stamp opis
----------- -------------------------------------------------- ----------------------- --------------------------------------------------
1 cc 2023-11-04 23:57:51.243 Procedura XX - cc
2 master 2023-11-04 23:59:00.287 Procedura XX - master
3 tempdb 2023-11-04 23:59:00.300 Procedura XX - tempdb
4 model 2023-11-04 23:59:00.303 Procedura XX - model
5 msdb 2023-11-04 23:59:00.317 Procedura XX - msdb
6 APBD23_ADM 2023-11-04 23:59:00.333 Procedura XX - APBD23_ADM
7 aa 2023-11-04 23:59:00.367 Procedura XX - aa
8 bb 2023-11-04 23:59:00.463 Procedura XX - bb
9 cc 2023-11-04 23:59:00.617 Procedura XX - cc
10 dd 2023-11-04 23:59:00.770 Procedura XX - dd
11 ee 2023-11-04 23:59:00.863 Procedura XX - ee
(11 rows affected)
check_id tb_nam tb_check_d_stamp LICZBA_REKORDOW
----------- -------------------------------------------------- ----------------------- ---------------
1 woj 2023-11-04 23:57:51.307 3
1 miasta 2023-11-04 23:57:51.310 6
1 osoby 2023-11-04 23:57:51.310 10 --baza cc z wieksza iloscia osob i etatow
1 firmy 2023-11-04 23:57:51.310 3
1 etaty 2023-11-04 23:57:51.310 15 --baza cc z wieksza iloscia osob i etatow
2 spt_fallback_db 2023-11-04 23:59:00.300 0
2 spt_fallback_dev 2023-11-04 23:59:00.300 0
2 spt_fallback_usg 2023-11-04 23:59:00.300 0
2 spt_monitor 2023-11-04 23:59:00.300 1
2 MSreplication_options 2023-11-04 23:59:00.300 3
6 CRDB_LOG 2023-11-04 23:59:00.360 5
6 CRUSR_LOG 2023-11-04 23:59:00.363 5
6 DB_CHECK 2023-11-04 23:59:00.363 6
6 DB_CHECK_ITEMS 2023-11-04 23:59:00.363 13
7 woj 2023-11-04 23:59:00.460 3
7 miasta 2023-11-04 23:59:00.460 6
7 osoby 2023-11-04 23:59:00.460 7
7 firmy 2023-11-04 23:59:00.460 3
7 etaty 2023-11-04 23:59:00.460 12
8 woj 2023-11-04 23:59:00.567 3
8 miasta 2023-11-04 23:59:00.567 6
8 osoby 2023-11-04 23:59:00.613 7
8 firmy 2023-11-04 23:59:00.617 3
8 etaty 2023-11-04 23:59:00.617 12
9 woj 2023-11-04 23:59:00.723 3
9 miasta 2023-11-04 23:59:00.723 6
9 osoby 2023-11-04 23:59:00.723 10 --baza cc z wieksza iloscia osob i etatow
9 firmy 2023-11-04 23:59:00.723 3
9 etaty 2023-11-04 23:59:00.770 15 --baza cc z wieksza iloscia osob i etatow
(29 rows affected)
*/
/*
4.4 Napisać procedurę, która dla parametru nazwa bazy,
nazwa tabeli wypisze historię
liczby rekordów dla podanej tabeli w podanej bazie
*/
GO
CREATE PROCEDURE proc44 (@db nvarchar(50), @t nvarchar(50))
AS
BEGIN
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = 'USE ' + QUOTENAME(@db) + ';
SELECT
dci.tb_check_d_stamp AS RecordCountDate,
dci.LICZBA_REKORDOW AS RecordCount,
dc.db_nam AS DatabaseName
FROM APBD23_ADM.dbo.DB_CHECK dc
JOIN APBD23_ADM.dbo.DB_CHECK_ITEMS dci ON dc.check_id = dci.check_id
WHERE dci.tb_nam = @t AND dc.db_nam = @db
ORDER BY dci.tb_check_d_stamp;'
EXEC sp_executesql @SQL, N'@t NVARCHAR(128), @db NVARCHAR(128)', @t, @db
END
EXEC proc44 @db = 'aa', @t = 'etaty'
/*
RecordCountDate RecordCount DatabaseName
----------------------- ----------- --------------------------------------------------
2023-11-04 23:57:51.310 15 cc
2023-11-04 23:59:00.770 15 cc
(2 rows affected)
RecordCountDate RecordCount DatabaseName
----------------------- ----------- --------------------------------------------------
2023-11-04 23:59:00.460 12 aa
(1 row affected)
*/