Have you ever tried to re- use a database and you need to purge its data, so that you maintain the database structures without its data for a test purpose on another server? Well, if that database is a real production system you will probably have some FK (foreign keys). Clearing a database of its data can be difficuralt with FK constraints. How do I mean? For example, if you run this syntax in the Query Analyzer:
TRUNCATE TABLE "table_name"
As you do this, you will somewhere run into an error saying you can't truncate a truncate a table because of a FK constraint. This is where the syntax below comes in. It will remove the constraints, truncate the tables, and place the constraints back.
use "Database_name"
SET NOCOUNT ON
GO
SELECT 'USE [' + db_name() +']';
SELECT 'ALTER TABLE ' +
'[' + s.name + '].[' + t.name + ']' +
' DROP CONSTRAINT [' + f.name +']'
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;
SELECT 'TRUNCATE TABLE ' + '[' + s.name + '].[' + t.name + ']'
FROM sys.TABLES t
INNER JOIN sys.schemas s ON t.schema_id=s.schema_id
WHERE t.is_ms_shipped=0;
SELECT '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
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 when you are done, don't forget to shrink the logfile size of the database you just truncated. Use below syntax.
DBCC SHRINKFILE('databaselog_Filename', TRUNCATEONLY)
