Truncate all the tables in a Database in SQL Server - SQL Server / TSQL Tutorial Part 55

Scenario:

Let's think about a database that we use for our ETL process and we call that database staging. We want to truncate all the tables in morning so those can be used for the daily process. Once way is to write our Truncate statement for each of the table but tables can be dropped or new tables can be created as well. We want to write a query that we can be use to Truncate all the tables from a database and we don't have to rewrite our Truncate statements.

If the tables do not have Primary Key -Foreign Key relationship, we can use Truncate. But we often have tables which are referenced by other tables. We can not use Truncate statement for those tables. The only way to Truncate those tables is to drop the Foreign Key Constraint before Truncating and then Truncate and Recreate Relationship after that.

The below Code can be used to Truncate all the tables from the database. If Tables will have the Pk-Fk relationship, The script will drop the Foreign Key Constraints first and then Truncate those tables and finally recreate the Foreign Key constraints.

Before you run below Script, Make sure you are pointing to correct Database and Server! All the best with Truncating all the tables from a SQL Server Database.


-- Drop Temp Tables if Exists
 IF OBJECT_ID('tempdb..#DropConstraint') IS NOT NULL
  
DROP TABLE #DropConstraint

 IF OBJECT_ID('tempdb..#CreateConstraint') IS NOT NULL
  
DROP TABLE #CreateConstraint

 IF OBJECT_ID('tempdb..#TempTruncateTables') IS NOT NULL
    
DROP TABLE #TempTruncateTables


-- Drop Constraint Script Save in #DropConstraint Temp Table 
SELECT 'ALTER TABLE ' + '['
      
+ Schema_name(o.schema_id) + '].['
      
+ OBJECT_NAME(FK.parent_object_id) + ']'
      
+ ' DROP  CONSTRAINT ' + '[' + FK.name + ']' AS DropConstraintQuery

 INTO   #DropConstraint
 FROM   sys.foreign_keys AS FK
      
INNER JOIN sys.foreign_key_columns AS FKC
              
ON FK.OBJECT_ID = FKC.constraint_object_id
      
INNER JOIN sys.objects O
              
ON O.OBJECT_ID = FKC.parent_object_id


-- Create Constraint Script Save in #CreateConstraint Temp Table 
SELECT 'ALTER TABLE ' + '['
      
+ Schema_name(o.schema_id) + '].' + '['
      
+ OBJECT_NAME(FK.parent_object_id) + ']'
      
+ ' ADD CONSTRAINT ' + '[' + FK.name
      
+ '] Foreign Key (['
      
+ (SELECT name
          
FROM   sys.columns c
          
WHERE  c.OBJECT_ID = FKC.parent_object_id
                
AND c.column_id = FKC.parent_column_id)
       +
']) REFERENCES ' + '['
      
+ Schema_name(o.schema_id) + '].['
      
+ (SELECT name
          
FROM   sys.objects o
          
WHERE  OBJECT_ID = FKC.referenced_object_id)
       +
'] (['
      
+ (SELECT name
          
FROM   sys.columns c
          
WHERE  c.OBJECT_ID = FKC.referenced_object_id
                
AND c.column_id = FKC.referenced_column_id)
       +
'])' AS CreateConstraintQuery

 INTO   #CreateConstraint
 FROM   sys.foreign_keys AS FK
      
INNER JOIN sys.foreign_key_columns AS FKC
              
ON FK.OBJECT_ID = FKC.constraint_object_id
      
INNER JOIN sys.objects o
              
ON FKC.parent_object_id = o.OBJECT_ID

-- Build Truncate Statement for all the tables and save into #TempTruncateTables
 SELECT 'Truncate table ' + Schema_name(schema_id)
       +
'.' + name AS TruncateTableQuery

 INTO   #TempTruncateTables 
FROM   sys.tables
 WHERE  TYPE = 'U'
      
AND is_ms_shipped = 0

GO


-- Drop Constraints
 DECLARE @DropConstraintQuery AS VARCHAR(4000)
 DECLARE DropConstraintCur CURSOR FOR
  SELECT
DropConstraintQuery
  
FROM   #DropConstraint

 OPEN DropConstraintCur 
FETCH Next FROM DropConstraintCur 
 INTO @DropConstraintQuery
 WHILE @@FETCH_STATUS = 0
  
BEGIN
      DECLARE
@SQL VARCHAR(MAX)=NULL
      
SET @SQL=@DropConstraintQuery
      
EXEC (@SQL)
      
PRINT ' Query ::' + @DropConstraintQuery
            
+ 'Completed'
      
FETCH Next FROM DropConstraintCur INTO @DropConstraintQuery
  
END
CLOSE
DropConstraintCur 

DEALLOCATE DropConstraintCur
GO
-- Truncate tables 

DECLARE @TempTruncateTablesCur AS VARCHAR(4000) 
DECLARE TempTruncateTablesCur CURSOR FOR
  SELECT
TruncateTableQuery
  
FROM   #TempTruncateTables 

OPEN TempTruncateTablesCur
 FETCH Next FROM TempTruncateTablesCur 
 INTO @TempTruncateTablesCur
 WHILE @@FETCH_STATUS = 0
  
BEGIN
      DECLARE
@SQL VARCHAR(MAX)=NULL

      
SET @SQL=@TempTruncateTablesCur

      
EXEC (@SQL)

      
PRINT ' Query ::' + @TempTruncateTablesCur
            
+ 'Completed'
      
FETCH Next FROM TempTruncateTablesCur INTO @TempTruncateTablesCur
  
END
CLOSE
TempTruncateTablesCur 

DEALLOCATE TempTruncateTablesCur
GO



-- Create Constraint After Truncate 
DECLARE @CreateConstraintQuery AS VARCHAR(4000) 
DECLARE CreateConstraintQueryCur CURSOR FOR
  SELECT
CreateConstraintQuery
  
FROM   #CreateConstraint

 OPEN CreateConstraintQueryCur 
FETCH Next FROM CreateConstraintQueryCur 
 INTO @CreateConstraintQuery 
WHILE @@FETCH_STATUS = 0
  
BEGIN
      DECLARE
@SQL VARCHAR(MAX)=NULL
      
SET @SQL=@CreateConstraintQuery
      
EXEC (@SQL)
      
PRINT ' Query ::' + @CreateConstraintQuery
            
+ 'Completed'
      
FETCH Next FROM CreateConstraintQueryCur INTO  @CreateConstraintQuery
  
END
CLOSE
CreateConstraintQueryCur

 DEALLOCATE CreateConstraintQueryCur

GO 




Things we covered in this Post
  • How to drop temp table if exists in SQL Server
  • How to use system tables to get Primary key and foreign key Constraint
  • How to use Cursors in SQL Server 
  • How to Drop Foreign Key Constraints dynamically and Recreate them
  • How to Truncate all tables in SQL Server Database
  • How to use Dynamic SQL in TSQL
  • How to use Variables in TSQL

3 comments:

  1. When I tried to run this script not all records are being removed.

    I get the following error Cannot TRUNCATE TABLE 'namexzy' because it is being referenced by object 'abc123'.

    ReplyDelete
  2. Cannot TRUNCATE TABLE 'xyz' because it is being referenced by object 'vwAbc123'. How do i get rid of the view

    ReplyDelete