Generating a Database Clean Up Script

Below is a script I recently used on a project to dynamically generate and execute the statements needed to clear all the data out of a database.  It is heavily borrowed from a blog post by Nikola Ilijev.  I just added the parts pertaining to the dropping and re-creating of the ColumnStore index objects and wrapped it in a dynamic sql harness so that it can be executed immediately.

Here’s a quick summary of the steps…

1. Drop all Foreign Key constraints
2. Drop all ColumnStore indexes
3. Truncate all tables
4. Re-Create all ColumnStore indexes
5. Re-Create all Foreign Key constraints

 

[sql]DECLARE @newline NVARCHAR(2) = CHAR(13) + CHAR(10),
@vSQL NVARCHAR(MAX) = ”
;

SELECT @vSQL = @vSQL +
‘USE [‘ + db_name() +’]’ + ‘;’ + @newline
;

— DROP ALL FK-CONSTRAINTS
SELECT @vSQL = @vSQL +
‘ALTER TABLE ‘ +
‘[‘ + s.name + ‘].[‘ + t.name + ‘]’ +
‘ DROP CONSTRAINT [‘ + f.name +’]’
+ ‘;’ + @newline
FROM sys.foreign_keys f
INNER JOIN sys.TABLES t ON f.parent_object_id=t.object_id
INNER JOIN sys.schemas s ON t.schema_id=s.schema_id
WHERE t.is_ms_shipped=0 AND
s.name IN (‘Stage’)
;

— DROP ALL COLUMNSTORE INDEXES
SELECT @vSQL = @vSQL +
‘DROP INDEX ‘ +
‘[‘ + i.name + ‘] ON ‘ +
‘[‘ + s.name + ‘].[‘ + t.name + ‘]’
+ ‘;’ + @newline
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE i.type_desc = ‘NONCLUSTERED COLUMNSTORE’ AND
t.is_ms_shipped=0 AND
s.name IN (‘Stage’)
;

— TRUNCATE ALL TABLES
SELECT @vSQL = @vSQL +
‘TRUNCATE TABLE ‘ + ‘[‘ + s.name + ‘].[‘ + t.name + ‘]’
+ ‘;’ + @newline
FROM sys.TABLES t
INNER JOIN sys.schemas s ON t.schema_id=s.schema_id
WHERE t.is_ms_shipped=0 AND
s.name IN (‘Stage’)
;

— RE-CREATE ALL COLUMNSTORE INDEXES
SELECT @vSQL = @vSQL +
‘CREATE NONCLUSTERED COLUMNSTORE INDEX ‘ +
‘[‘ + i.name + ‘] ON ‘ +
‘[‘ + s.name + ‘].[‘ + t.name + ‘] (‘ +
STUFF(
( SELECT ‘, [‘ + c.name + ‘]’
FROM sys.index_columns ic
INNER JOIN sys.columns c
ON c.object_id = ic.object_id AND
c.column_id = ic.column_id
WHERE ic.object_id = i.object_id AND
ic.index_id = i.index_id
ORDER BY ic.index_column_id
FOR XML Path(”)
), 1,2,”
) + ‘)’
+ ‘;’ + @newline
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE t.is_ms_shipped=0 AND
s.name IN (‘Stage’)
;

— RECREATE ALL FK-CONSTRAINTS
SELECT @vSQL = @vSQL +
‘ALTER TABLE ‘ +
‘[‘ + s.name + ‘].[‘ + t.name + ‘]’ +
‘ ADD CONSTRAINT [‘ + f.name + ‘]’ +
‘ FOREIGN KEY (‘ +
Stuff( (SELECT ‘, [‘+col_name(fk.parent_object_id, fk.parent_column_id) +’]’
FROM sys.foreign_key_columns fk
WHERE constraint_object_id = f.object_id
ORDER BY constraint_column_id
FOR XML Path(”)
), 1,2,”) + ‘)’ +
‘ REFERENCES [‘ +
object_schema_name(f.referenced_object_id)+’].[‘+object_name(f.referenced_object_id) + ‘] (‘ +
Stuff((SELECT ‘, [‘+col_name(fc.referenced_object_id, fc.referenced_column_id)+’]’
FROM sys.foreign_key_columns fc
WHERE constraint_object_id = f.object_id
ORDER BY constraint_column_id
FOR XML Path(”)),
1,2,”) +
‘)’ +
‘ ON DELETE ‘ + REPLACE(f.delete_referential_action_desc, ‘_’, ‘ ‘) +
‘ ON UPDATE ‘ + REPLACE(f.update_referential_action_desc , ‘_’, ‘ ‘) collate database_default
+ ‘;’ + @newline
FROM sys.foreign_keys f
INNER JOIN sys.TABLES t ON f.parent_object_id=t.object_id
INNER JOIN sys.schemas s ON t.schema_id=s.schema_id
WHERE t.is_ms_shipped=0 AND
s.name IN (‘Stage’)
;

EXEC (@vSQL)[/sql]