· 7 years ago · Oct 17, 2018, 07:14 AM
1-- disable referential integrity
2EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
3GO
4
5EXEC sp_MSForEachTable 'DELETE FROM ?'
6GO
7
8-- enable referential integrity again
9EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'
10GO
11
12-- disable referential integrity
13EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
14GO
15
16EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'
17GO
18
19-- enable referential integrity again
20EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
21GO
22
23/* Drop all non-system stored procs */
24DECLARE @name VARCHAR(128)
25DECLARE @SQL VARCHAR(254)
26
27SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])
28
29WHILE @name is not null
30BEGIN
31 SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'
32 EXEC (@SQL)
33 PRINT 'Dropped Procedure: ' + @name
34 SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
35END
36GO
37
38/* Drop all views */
39DECLARE @name VARCHAR(128)
40DECLARE @SQL VARCHAR(254)
41
42SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name])
43
44WHILE @name IS NOT NULL
45BEGIN
46 SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']'
47 EXEC (@SQL)
48 PRINT 'Dropped View: ' + @name
49 SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name])
50END
51GO
52
53/* Drop all functions */
54DECLARE @name VARCHAR(128)
55DECLARE @SQL VARCHAR(254)
56
57SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])
58
59WHILE @name IS NOT NULL
60BEGIN
61 SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']'
62 EXEC (@SQL)
63 PRINT 'Dropped Function: ' + @name
64 SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name])
65END
66GO
67
68/* Drop all Foreign Key constraints */
69DECLARE @name VARCHAR(128)
70DECLARE @constraint VARCHAR(254)
71DECLARE @SQL VARCHAR(254)
72
73SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
74
75WHILE @name is not null
76BEGIN
77 SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
78 WHILE @constraint IS NOT NULL
79 BEGIN
80 SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'
81 EXEC (@SQL)
82 PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name
83 SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
84 END
85SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
86END
87GO
88
89/* Drop all Primary Key constraints */
90DECLARE @name VARCHAR(128)
91DECLARE @constraint VARCHAR(254)
92DECLARE @SQL VARCHAR(254)
93
94SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
95
96WHILE @name IS NOT NULL
97BEGIN
98 SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
99 WHILE @constraint is not null
100 BEGIN
101 SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']'
102 EXEC (@SQL)
103 PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name
104 SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
105 END
106SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
107END
108GO
109
110/* Drop all tables */
111DECLARE @name VARCHAR(128)
112DECLARE @SQL VARCHAR(254)
113
114SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])
115
116WHILE @name IS NOT NULL
117BEGIN
118 SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
119 EXEC (@SQL)
120 PRINT 'Dropped Table: ' + @name
121 SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
122END
123GO
124
125sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all';
126
127SET NOCOUNT ON
128GO
129
130SELECT 'USE [' + db_name() +']';
131;WITH a AS
132(
133 SELECT 0 AS lvl,
134 t.object_id AS tblID
135 FROM sys.TABLES t
136 WHERE t.is_ms_shipped = 0
137 AND t.object_id NOT IN (SELECT f.referenced_object_id
138 FROM sys.foreign_keys f)
139
140 UNION ALL
141
142 SELECT a.lvl + 1 AS lvl,
143 f.referenced_object_id AS tblId
144 FROM a
145 INNER JOIN sys.foreign_keys f ON a.tblId = f.parent_object_id
146 AND a.tblID <> f.referenced_object_id
147)
148SELECT
149 'Delete from ['+ object_schema_name(tblID) + '].[' + object_name(tblId) + ']'
150FROM a
151GROUP BY tblId
152ORDER BY MAX(lvl),1
153
154sp_msforeachtable 'ALTER TABLE ? ENABLE TRIGGER all'
155
156begin transaction
157commit;
158
159------------------------------------------------------------
160/* Use database */
161-------------------------------------------------------------
162
163use somedatabase;
164
165GO
166
167------------------------------------------------------------------
168/* Script to delete an repopulate the base [init database] */
169------------------------------------------------------------------
170
171-------------------------------------------------------------
172/* Procedure delete all constraints */
173-------------------------------------------------------------
174
175IF EXISTS (SELECT name
176 FROM sysobjects
177 WHERE name = 'sp_DeleteAllConstraints' AND type = 'P')
178 DROP PROCEDURE dbo.sp_DeleteAllConstraints
179GO
180
181CREATE PROCEDURE sp_DeleteAllConstraints
182AS
183 EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
184 EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'
185GO
186
187-----------------------------------------------------
188/* Procedure delete all data from the database */
189-----------------------------------------------------
190
191IF EXISTS (SELECT name
192 FROM sysobjects
193 WHERE name = 'sp_DeleteAllData' AND type = 'P')
194 DROP PROCEDURE dbo.sp_DeleteAllData
195GO
196
197CREATE PROCEDURE sp_DeleteAllData
198AS
199 EXEC sp_MSForEachTable 'DELETE FROM ?'
200GO
201
202-----------------------------------------------
203/* Procedure enable all constraints */
204-----------------------------------------------
205
206IF EXISTS (SELECT name
207 FROM sysobjects
208 WHERE name = 'sp_EnableAllConstraints' AND type = 'P')
209 DROP PROCEDURE dbo.sp_EnableAllConstraints
210GO
211-- ....
212-- ....
213-- ....
214
215EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
216
217EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'
218
219EXEC sp_MSForEachTable 'DELETE FROM ?'
220
221EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
222
223EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL'
224
225EXEC sp_MSFOREACHTABLE 'SELECT * FROM ?'
226
227GO