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.
