Things Google Staff Do

TRUNCATE TABLES, REMOVE CONSTRAINTS, RESTORE CONSTRAINTS

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)