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.

How to import large SQL files in database

I recently faced an situation, where i was supplied with a .SQL file which was exported from a large table with some million records. The file was exported from the generate script options of SSMS and  had the insert statements. The size of the file was more than 2GB.

SSMS(SQL Server Management Studio) is not capable enough of handling such big files. It would through one following error:

• Insufficient memory to continue the execution of the program.
• or SSMS would simply reject the request to open the file.

SQLCMD is the life saver here. We could use SQL command line utility to execute the T-SQL commands. It is pretty simple to use as detailed below:

1. Open the Command prompt with the administrator privileges.
2. Execute the following command.

 sqlcmd -S SQLSERVER\InstanceName -i "F:\File.sql"

The difference: SSMS uses the .Net framework for execution where as the command line uses ODBC driver.