-
Notifications
You must be signed in to change notification settings - Fork 671
/
dbWarden_UNINSTALL.sql
300 lines (277 loc) · 11.2 KB
/
dbWarden_UNINSTALL.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
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
IF EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'tr_DDL_SchemaChangeLog')
BEGIN
DROP TRIGGER [tr_DDL_SchemaChangeLog] ON DATABASE
END
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'usp_JobStats' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE')
BEGIN
DROP PROC dbo.usp_JobStats
END
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'usp_PerfStats' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE')
BEGIN
DROP PROC dbo.usp_PerfStats
END
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'usp_MemoryUsageStats' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE')
BEGIN
DROP PROC dbo.usp_MemoryUsageStats
END
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'usp_CPUStats' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE')
BEGIN
DROP PROC dbo.usp_CPUStats
END
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'usp_CPUProcessAlert' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE')
BEGIN
DROP PROC dbo.usp_CPUProcessAlert
END
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'usp_LongRunningJobs' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE')
BEGIN
DROP PROC dbo.usp_LongRunningJobs
END
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'usp_LongRunningQueries' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE')
BEGIN
DROP PROC dbo.usp_LongRunningQueries
END
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'usp_CheckBlocking' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE')
BEGIN
DROP PROC dbo.usp_CheckBlocking
END
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'usp_FileStats' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE')
BEGIN
DROP PROC dbo.usp_FileStats
END
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'usp_CheckFilesWork' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE')
BEGIN
DROP PROC dbo.usp_CheckFilesWork
END
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'usp_CheckFiles' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE')
BEGIN
DROP PROC dbo.usp_CheckFiles
END
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'dd_PopulateDataDictionary' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE')
BEGIN
DROP PROC dbo.dd_PopulateDataDictionary
END
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'dd_UpdateDataDictionaryTable' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE')
BEGIN
DROP PROC dbo.dd_UpdateDataDictionaryTable
END
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'dd_UpdateDataDictionaryField' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE')
BEGIN
DROP PROC dbo.dd_UpdateDataDictionaryField
END
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'dd_TestDataDictionaryTables' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE')
BEGIN
DROP PROC dbo.dd_TestDataDictionaryTables
END
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'dd_TestDataDictionaryFields' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE')
BEGIN
DROP PROC dbo.dd_TestDataDictionaryFields
END
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'dd_ApplyDataDictionary' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE')
BEGIN
DROP PROC dbo.dd_ApplyDataDictionary
END
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'dd_ScavengeDataDictionaryTables' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE')
BEGIN
DROP PROC dbo.dd_ScavengeDataDictionaryTables
END
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'dd_ScavengeDataDictionaryFields' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE')
BEGIN
DROP PROC dbo.dd_ScavengeDataDictionaryFields
END
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'sp_ViewTableExtendedProperties' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE')
BEGIN
DROP PROC dbo.sp_ViewTableExtendedProperties
END
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'usp_TodaysDeadlocks' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE')
BEGIN
DROP PROC dbo.usp_TodaysDeadlocks
END
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'rpt_Queries' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE')
BEGIN
DROP PROC dbo.rpt_Queries
END
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'rpt_Blocking' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE')
BEGIN
DROP PROC dbo.rpt_Blocking
END
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'rpt_JobHistory' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE')
BEGIN
DROP PROC dbo.rpt_JobHistory
END
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'rpt_HealthReport' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE')
BEGIN
DROP PROC dbo.rpt_HealthReport
END
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'gen_GetHealthReportHTML' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE')
BEGIN
DROP PROC dbo.gen_GetHealthReportHTML
END
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'gen_GetHealthReportToEmail' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE')
BEGIN
DROP PROC dbo.gen_GetHealthReportToEmail
END
GO
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'gen_GetHealthReportToFile' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE')
BEGIN
DROP PROC dbo.gen_GetHealthReportToFile
END
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DataDictionary_Fields' AND TABLE_SCHEMA = 'dbo')
BEGIN
DROP TABLE dbo.DataDictionary_Fields
END
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DataDictionary_Tables' AND TABLE_SCHEMA = 'dbo')
BEGIN
DROP TABLE dbo.DataDictionary_Tables
END
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CPUStatsHistory' AND TABLE_SCHEMA = 'dbo')
BEGIN
DROP TABLE dbo.CPUStatsHistory
END
GO
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[ti_blockinghistory]'))
DROP TRIGGER [dbo].[ti_blockinghistory]
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'BlockingHistory' AND TABLE_SCHEMA = 'dbo')
BEGIN
DROP TABLE dbo.BlockingHistory
END
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'HealthReport' AND TABLE_SCHEMA = 'dbo')
BEGIN
DROP TABLE dbo.HealthReport
END
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'FileStatsHistory' AND TABLE_SCHEMA = 'dbo')
BEGIN
DROP TABLE dbo.FileStatsHistory
END
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'JobStatsHistory' AND TABLE_SCHEMA = 'dbo')
BEGIN
DROP TABLE dbo.JobStatsHistory
END
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'MemoryUsageHistory' AND TABLE_SCHEMA = 'dbo')
BEGIN
DROP TABLE dbo.MemoryUsageHistory
END
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'PerfStatsHistory' AND TABLE_SCHEMA = 'dbo')
BEGIN
DROP TABLE dbo.PerfStatsHistory
END
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'QueryHistory' AND TABLE_SCHEMA = 'dbo')
BEGIN
DROP TABLE dbo.QueryHistory
END
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'SchemaChangeLog' AND TABLE_SCHEMA = 'dbo')
BEGIN
DROP TABLE dbo.SchemaChangeLog
END
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'AlertSettings' AND TABLE_SCHEMA = 'dbo')
BEGIN
DROP TABLE dbo.AlertSettings
END
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DatabaseSettings' AND TABLE_SCHEMA = 'dbo')
BEGIN
DROP TABLE dbo.DatabaseSettings
END
GO
DECLARE @USER VARCHAR(50),
@droploginsql VARCHAR(100),
@sqlexec VARCHAR(max),
@dbname VARCHAR(200)
SELECT name, 0 as status
INTO #databases
FROM sys.databases
WHERE database_id > 4
WHILE EXISTS (SELECT name FROM #databases WHERE status = 0)
BEGIN
SET @dbname = (SELECT top 1 name FROM #databases WHERE status = 0)
SELECT @sqlexec = 'USE '+ char(13) + '[' + name + ']' + CHAR(13)+ CHAR(10) +
+ 'IF EXISTS (SELECT * FROM sys.triggers WHERE [name] = ''tr_DDL_SchemaChangeLog'') DROP TRIGGER tr_DDL_SchemaChangeLog ON DATABASE'
+ CHAR(13) + 'IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''SchemaChangeLog'' AND TABLE_SCHEMA = ''dbo'') DROP TABLE dbo.SchemaChangeLog'
FROM #databases
WHERE name = @dbname
UPDATE #databases
SET status = 1
WHERE name = @dbname
EXEC (@sqlexec)
END
print 'done'
DROP TABLE #databases
USE [msdb]
GO
/****** Object: Job [dbWarden_BlockingAlert] Script Date: 04/15/2013 09:22:26 ******/
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'dbWarden_BlockingAlert')
EXEC msdb.dbo.sp_delete_job @job_name=N'dbWarden_BlockingAlert', @delete_unused_schedule=1
GO
/****** Object: Job [dbWarden_BlockingAlert] Script Date: 04/15/2013 09:22:26 ******/
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'dbWarden_CheckFiles')
EXEC msdb.dbo.sp_delete_job @job_name=N'dbWarden_CheckFiles', @delete_unused_schedule=1
GO
/****** Object: Job [dbWarden_BlockingAlert] Script Date: 04/15/2013 09:22:26 ******/
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'dbWarden_CPUAlert')
EXEC msdb.dbo.sp_delete_job @job_name=N'dbWarden_CPUAlert', @delete_unused_schedule=1
GO
/****** Object: Job [dbWarden_BlockingAlert] Script Date: 04/15/2013 09:22:26 ******/
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'dbWarden_HealthReport')
EXEC msdb.dbo.sp_delete_job @job_name=N'dbWarden_HealthReport', @delete_unused_schedule=1
GO
/****** Object: Job [dbWarden_BlockingAlert] Script Date: 04/15/2013 09:22:26 ******/
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'dbWarden_LongRunningJobsAlert')
EXEC msdb.dbo.sp_delete_job @job_name=N'dbWarden_LongRunningJobsAlert', @delete_unused_schedule=1
GO
/****** Object: Job [dbWarden_BlockingAlert] Script Date: 04/15/2013 09:22:26 ******/
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'dbWarden_LongRunningQueriesAlert')
EXEC msdb.dbo.sp_delete_job @job_name=N'dbWarden_LongRunningQueriesAlert', @delete_unused_schedule=1
GO
/****** Object: Job [dbWarden_BlockingAlert] Script Date: 04/15/2013 09:22:26 ******/
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'dbWarden_MemoryUsageStats')
EXEC msdb.dbo.sp_delete_job @job_name=N'dbWarden_MemoryUsageStats', @delete_unused_schedule=1
GO
/****** Object: Job [dbWarden_BlockingAlert] Script Date: 04/15/2013 09:22:26 ******/
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'dbWarden_PerfStats')
EXEC msdb.dbo.sp_delete_job @job_name=N'dbWarden_PerfStats', @delete_unused_schedule=1
GO
IF EXISTS (SELECT name FROM msdb.dbo.sysoperators WHERE name = N'SQL_DBA')
EXEC msdb.dbo.sp_delete_operator @name=N'SQL_DBA'
GO
IF EXISTS (SELECT name FROM msdb.dbo.sysoperators WHERE name = N'SQL_DBA_vtext')
EXEC msdb.dbo.sp_delete_operator @name=N'SQL_DBA_vtext'
GO
DROP DATABASE dbWarden;
GO