Truncate Microsoft SQL Database without manually dropping Key constraints

We all have faced times when we want to truncate all the tables of a database, but to truncate a table we need to drop the all the constarints. It is painful to drop all constraints, than truncate and recreate the constarints.
If it is a small database this trick might work but for a big database it is next to impossible, specially when your database structure is changing over time.
Although there is no out of the box way to do it, but with a few line of code we could surely do it.

Following peice of code would do the trick. We can also create a procedure for it if needed regularly.

We will need 3 tables to store the data temporarily. Let's first check if they already exists:
IF OBJECT_ID('dbo.DropFkey', 'U') IS NOT NULL
    DROP TABLE dbo.DropFkey;
IF OBJECT_ID('dbo.DROPTruncate', 'U') IS NOT NULL
    DROP TABLE dbo.DROPTruncate;
    IF OBJECT_ID('dbo.CreateConstraints', 'U') IS NOT NULL
    DROP TABLE dbo.CreateConstraints;

Next step is to create a list of all the primary keys and store it into a table.
;WITH cte(consColumn,
         foreignKeyName,
         parentSchema,
         parentTableName,
         parentColName,
         refSchema,
         refTableName,
         refColName)
     AS (SELECT fkc.constraint_column_id AS consColumn,
                fk.NAME AS foreignKeyName,
                parentSchema.name AS parentSchema,
                parentTable.NAME AS parentTableName,
                parent_col.NAME AS parentColName,
                refSchema.name AS refSchema,
                refTable.NAME AS refTableName,
                ref_col.NAME AS refColName
         FROM sys.foreign_keys fk
              INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
              INNER JOIN sys.tables parentTable ON parentTable.object_id = fkc.parent_object_id
              INNER JOIN sys.schemas parentSchema ON parentSchema.schema_id = parentTable.schema_id
              INNER JOIN sys.columns parent_col ON fkc.parent_column_id = parent_col.column_id
                                                   AND parent_col.object_id = parentTable.object_id
              INNER JOIN sys.tables refTable ON refTable.object_id = fkc.referenced_object_id
              INNER JOIN sys.schemas refSchema ON refSchema.schema_id = refTable.schema_id
              INNER JOIN sys.columns ref_col ON fkc.referenced_column_id = ref_col.column_id
                                                AND ref_col.object_id = refTable.object_id
                                                AND parentTable.type = 'U'
                                                AND refTable.type = 'U')
     SELECT DISTINCT
            foreignKeyName,
            parentSchema,
            parentTableName,
            SUBSTRING(
                     (
                         SELECT ','+a.parentColName+''
                         FROM cte a
                         WHERE a.foreignKeyName = c.foreignKeyName
                         ORDER BY a.consColumn
                         FOR XML PATH('')
                     ), 2, 200000) AS parentColName,
            refSchema,
            refTableName,
            SUBSTRING(
                     (
                         SELECT ','+b.refColName+''
                         FROM cte b
                         WHERE b.foreignKeyName = c.foreignKeyName
                         ORDER BY b.consColumn
                         FOR XML PATH('')
                     ), 2, 200000) AS refColName
    INTO #FkeyDesc
     FROM cte c;

Now that we have the list of all the foreign keys, we can drop the keys in the table. We would use the list of keys stored above to re-create the keys post truncation.
SELECT DISTINCT Row_Number() OVER ( ORDER BY foreignKeyName ASC ) AS RN,
       'IF  EXISTS 
    (SELECT * FROM sys.foreign_keys 
WHERE 
object_id = OBJECT_ID(N''['+parentSchema+'].['+foreignKeyName+']'') 
AND 
parent_object_id 
= OBJECT_ID(N''['+parentSchema+'].['+ParentTableName+']''))
ALTER TABLE ['+parentSchema+'].['+ParentTableName+'] 
DROP CONSTRAINT ['+foreignKeyName+']' AS foreignKey_drop_script
INTO DropFkey
FROM #FkeyDesc;
DECLARE @Job NVARCHAR(MAX);
DECLARE @RN INT;
DECLARE @RN1 INT;
SELECT TOP 1 @Job = foreignKey_drop_script,
             @RN = RN
FROM DropFkey
ORDER BY RN;

WHILE @RN IS NOT NULL
    BEGIN
        EXEC sp_executesql
             @Job;
        SET @RN1 = @RN;
        SELECT TOP 1 @Job = foreignKey_drop_script,
                     @RN = RN
        FROM DropFkey
        WHERE RN > @Rn1
        ORDER BY RN;
   IF @@ROWCOUNT = 0
        BREAK
    END;


As the keys are dropped we could simply truncate all the tables, with the exception of tables which stores the list of keys to be regenerated
SELECT DISTINCT ROW_NUMBER () OVER  (ORDER BY Name) AS RN,
' TRUNCATE TABLE '+ Name + '' AS TruncateScript
INTO DROPTruncate
FROM sys.tables
WHERE type_desc = 'USER_TABLE'
AND NAME NOT IN
(
'DropFkey',
'DropTruncate',
'CreateConstraints'
--If neede we can add more tables here and these tables would not be truncated.
) 
DECLARE @RNTruncate INT;
DECLARE @RNTruncate1 INT;
DECLARE @JobTruncate NVARCHAR(MAX);
SELECT TOP 1 @JobTruncate = TruncateScript,
             @RNTruncate = RN
FROM DROPTruncate
ORDER BY RN;
WHILE @RNTruncate IS NOT NULL
    BEGIN
        EXEC sp_executesql
             @Jobtruncate;
        SET @RNTruncate1 = @RNTruncate;
        SELECT TOP 1 @JobTruncate = TruncateScript,
                     @RNTruncate = RN
        FROM DROPTruncate
        WHERE RN > @RNTruncate1
        ORDER BY RN;      
        IF @@ROWCOUNT = 0 BREAK;
    END;


Now that all the tables are truncate we could simply re-create the keys.
SELECT DISTINCT
        ROW_NUMBER () OVER  (ORDER BY foreignKeyName) AS RN,
       'ALTER TABLE ['+parentSchema+'].['+ParentTableName+']  WITH CHECK 
ADD  CONSTRAINT ['+foreignKeyName+'] FOREIGN KEY('+parentColName+')
REFERENCES ['+refSchema+'].['+refTableName+']('+refcolname+')' AS Add_constraints_script
INTO CreateConstraints
FROM #FkeyDesc; 
DECLARE @RNCREATE INT;
DECLARE @RNCREATE1 INT;
DECLARE @JobCREATE NVARCHAR(MAX);
SELECT TOP 1 @JobCREATE = Add_constraints_script,
             @RNCREATE = RN
FROM CreateConstraints
ORDER BY RN;
WHILE @RNCREATE IS NOT NULL
    BEGIN
        EXEC sp_executesql
             @JobCREATE;
        SET @RNCREATE1 = @RNCREATE;
        SELECT TOP 1 @JobCREATE = Add_constraints_script,
                     @RNCREATE = RN
        FROM CreateConstraints
        WHERE RN > @RNCREATE1
        ORDER BY RN;
        IF @@ROWCOUNT = 0 BREAK;
 END

Performance: It seems like a gigantic piece of code, but i have tested it on database with more than 100 tables and a few million records. Execution time was approx 1 minute.

Feel free to post your queries in comments and follow this blog if you liked the post.