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