· 6 years ago · Jul 23, 2019, 01:10 PM
1CREATE PROCEDURE
2[dbo].[truncate_non_empty_table]
3@TableToTruncate VARCHAR(64)
4AS
5BEGIN
6SET NOCOUNT ON
7-- GLOBAL VARIABLES
8DECLARE @i int
9DECLARE @Debug bit
10DECLARE @Recycle bit
11DECLARE @Verbose bit
12DECLARE @TableName varchar(80)
13DECLARE @ColumnName varchar(80)
14DECLARE @ReferencedTableName varchar(80)
15DECLARE @ReferencedColumnName varchar(80)
16DECLARE @ConstraintName varchar(250)
17DECLARE @CreateStatement varchar(max)
18DECLARE @DropStatement varchar(max)
19DECLARE @TruncateStatement varchar(max)
20DECLARE @CreateStatementTemp varchar(max)
21DECLARE @DropStatementTemp varchar(max)
22DECLARE @TruncateStatementTemp varchar(max)
23DECLARE @Statement varchar(max)
24 -- 1 = Will not execute statements
25SET @Debug = 0
26 -- 0 = Will not create or truncate storage table
27 -- 1 = Will create or truncate storage table
28SET @Recycle = 0
29 -- 1 = Will print a message on every step
30set @Verbose = 1
31SET @i = 1
32
33 SET @CreateStatement = 'ALTER TABLE [dbo].[<tablename>] WITH NOCHECK
34ADD CONSTRAINT [<constraintname>] FOREIGN KEY([<column>]) REFERENCES [dbo].[<reftable>]
35([<refcolumn>])'
36 SET @DropStatement = 'ALTER TABLE [dbo].[<tablename>] DROP CONSTRAINT [<constraintname>]'
37 SET @TruncateStatement = 'TRUNCATE TABLE [<tablename>]'
38
39-- Drop Temporary tables
40
41IF OBJECT_ID('tempdb..#FKs') IS NOT NULL
42 DROP TABLE #FKs
43-- GET FKs
44SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(parent_object_id), clm1.name) as ID,
45 OBJECT_NAME(constraint_object_id) as ConstraintName,
46 OBJECT_NAME(parent_object_id) as TableName,
47 clm1.name as ColumnName,
48 OBJECT_NAME(referenced_object_id) as ReferencedTableName,
49 clm2.name as ReferencedColumnName
50 INTO #FKs
51 FROM sys.foreign_key_columns fk
52 JOIN sys.columns clm1
53 ON fk.parent_column_id = clm1.column_id
54 AND fk.parent_object_id = clm1.object_id
55 JOIN sys.columns clm2 ON fk.referenced_column_id = clm2.column_id
56 AND fk.referenced_object_id= clm2.object_id
57 --WHERE OBJECT_NAME(parent_object_id) not in ('//tables that you do not wont to be truncated')
58WHERE OBJECT_NAME(referenced_object_id) = @TableToTruncate
59ORDER BY OBJECT_NAME(parent_object_id)
60
61-- Prepare Storage Table
62IF Not EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Internal_FK_Definition_Storage')
63 BEGIN
64 IF @Verbose = 1
65 PRINT '1. Creating Process Specific Tables...'
66
67 -- CREATE STORAGE TABLE IF IT DOES NOT EXISTS
68 CREATE TABLE [Internal_FK_Definition_Storage]
69 (
70 ID int not null identity(1,1) primary key,
71 FK_Name varchar(250) not null,
72 FK_CreationStatement varchar(max) not null,
73 FK_DestructionStatement varchar(max) not null,
74 Table_TruncationStatement varchar(max) not null
75 )
76 END
77ELSE
78 BEGIN
79 IF @Recycle = 0
80 BEGIN
81 IF @Verbose = 1
82 PRINT '1. Truncating Process Specific Tables...'
83
84 -- TRUNCATE TABLE IF IT ALREADY EXISTS
85 TRUNCATE TABLE [Internal_FK_Definition_Storage]
86 END
87 ELSE
88 PRINT '1. Process specific table will be recycled from previous execution...'
89 END
90
91IF @Recycle = 0
92 BEGIN
93 IF @Verbose = 1
94 PRINT '2. Backing up Foreign Key Definitions...'
95
96-- Fetch and persist FKs
97 WHILE (@i <= (SELECT MAX(ID) FROM #FKs))
98 BEGIN
99 SET @ConstraintName = (SELECT ConstraintName FROM #FKs WHERE ID = @i)
100 SET @TableName = (SELECT TableName FROM #FKs WHERE ID = @i)
101 SET @ColumnName = (SELECT ColumnName FROM #FKs WHERE ID = @i)
102 SET @ReferencedTableName = (SELECT ReferencedTableName FROM #FKs WHERE ID = @i)
103 SET @ReferencedColumnName = (SELECT ReferencedColumnName FROM #FKs WHERE ID = @i)
104 SET @DropStatementTemp = REPLACE(REPLACE(@DropStatement,'<tablename>',@TableName),'<constraintname>',@ConstraintName)
105 SET @CreateStatementTemp = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@CreateStatement,'<tablename>',@TableName),'<column>',@ColumnName),'<constraintname>',@ConstraintName),'<reftable>',@ReferencedTableName),'<refcolumn>',@ReferencedColumnName)
106 SET @TruncateStatementTemp = REPLACE(@TruncateStatement,'<tablename>',@TableName)
107
108 INSERT INTO [Internal_FK_Definition_Storage]
109 SELECT @ConstraintName, @CreateStatementTemp, @DropStatementTemp, @TruncateStatementTemp
110 SET @i = @i + 1
111 IF @Verbose = 1
112 PRINT ' > Backing up [' + @ConstraintName + '] from [' + @TableName + ']'
113 END
114 END
115 ELSE
116 PRINT '2. Backup up was recycled from previous execution...'
117 IF @Verbose = 1
118 PRINT '3. Dropping Foreign Keys...'
119
120 -- DROP FOREIGN KEYS
121 SET @i = 1
122 WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
123 BEGIN
124 SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
125 SET @Statement = (SELECT FK_DestructionStatement FROM [Internal_FK_Definition_Storage] WITH (NOLOCK) WHERE ID = @i)
126
127 IF @Debug = 1
128 PRINT @Statement
129 ELSE
130 EXEC(@Statement)
131
132 SET @i = @i + 1
133
134
135 IF @Verbose = 1
136 PRINT ' > Dropping [' + @ConstraintName + ']'
137 END
138
139 IF @Verbose = 1
140 PRINT '4. Truncating Tables...'
141
142-- TRUNCATE TABLES
143-- Commented out as the tables to be truncated might also contain tables that has foreign keys
144-- to resolve this the stored procedure should be called recursively, but has not yet been implemented yet
145 /*
146 SET @i = 1
147 WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage])
148 BEGIN
149 SET @Statement = (SELECT Table_TruncationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i
150
151 IF @Debug = 1
152 PRINT @Statement
153 ELSE
154 EXEC(@Statement)
155
156 SET @i = @i + 1
157
158 IF @Verbose = 1
159 PRINT ' > ' + @Statement
160 END
161*/
162 IF @Verbose = 1
163 PRINT ' > TRUNCATE TABLE [' + @TableToTruncate + ']'
164 IF @Debug = 1
165 PRINT 'TRUNCATE TABLE [' + @TableToTruncate + ']'
166 ELSE
167 EXEC('TRUNCATE TABLE [' + @TableToTruncate + ']')
168
169 IF @Verbose = 1
170 PRINT '5. Re-creating Foreign Keys...'
171
172 -- CREATE FOREIGN KEYS
173 SET @i = 1
174 WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
175 BEGIN
176 SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
177 SET @Statement = (SELECT FK_CreationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
178
179 IF @Debug = 1
180 PRINT @Statement
181 ELSE
182 EXEC(@Statement)
183 SET @i = @i + 1
184
185 IF @Verbose = 1
186 PRINT ' > Re-creating [' + @ConstraintName + ']'
187 END
188 IF @Verbose = 1
189 PRINT '6. Process Completed'
190END
191GO