Delete All Table Data Within a Database in SQL Server

Problem statement

One of my project assignments was to clean an entire database, in other words delete all the data from all the tables of the database. My database has more than 800 tables so it was not possible to write a delete query on each and every table and each table having many constraints like primary key and foreign key.

So manually writing a query is a very tedious job.

Solution

I have found two ways to delete all the table data within a database. In this article I will explain each way one by one.

1. Using Undocumented Stored Procedure: sp_MSForEachTable

The Stored Procedure "sp_MSforeachtable" allows us to easily process some code against each and every table in a single database. It means that it is used to process a single T-SQL command or a number of different T-SQL commands against every table in the database.

Using the following procedure we can delete all the data from all the tables of a single database.

Step 1: Disable all constraints on the database

  1. EXEC sys.sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 

Step 2: Do a Delete or truncate operation on each table of the database

  1. EXEC sys.sp_msforeachtable 'DELETE FROM ?' 

Step 3: Enable all constrains on the database

  1. EXEC sys.sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL' 
2. Using INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.TABLE_CONSTRAINTS system tables

The INFORMATION_SCHEMA.TABLES view allows us to get information about all the tables and views within the database. INFORMATION_SCHEMA.TABLE_CONSTRAINTS returns the information about the table constraints in the current database. Using the following procedure we can delete all the data from all the tables in the database.

Step 1: Determine all child tables and tables that have no relations. Perform the delete operation on those tables.

  1.  DECLARE @tableName VARCHAR(200)  
  2. SET @tableName=''  
  3. WHILE EXISTS  
  4.  (  
  5.  --Find all child tables AND those which have no relations  
  6.              SELECT T.table_name FROM INFORMATION_SCHEMA.TABLES T  
  7.              LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC  
  8.              ON T.table_name=TC.table_name  
  9.              WHERE (TC.constraint_Type ='Foreign Key'or TC.constraint_Type IS NULLAND  
  10.              T.table_name NOT IN ('dtproperties','sysconstraints','syssegments')AND  
  11.              Table_type='BASE TABLE' AND T.table_name > @TableName  
  12.  )  
  13.  BEGIN  
  14.              SELECT @tableName=min(T.table_name) FROM INFORMATION_SCHEMA.TABLES T  
  15.              LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC  
  16.              ON T.table_name=TC.table_name  
  17.              WHERE (TC.constraint_Type ='Foreign Key'or TC.constraint_Type IS NULLAND  
  18.              T.table_name NOT IN ('dtproperties','sysconstraints','syssegments'AND  
  19.              Table_type='BASE TABLE' AND T.table_name > @TableName  
  20.              --Truncate the table  
  21.              EXEC('DELETE FROM '+@tablename)  
  22.      PRINT 'DELETE FROM '+@tablename  
  23.  END 

Step 2: Determine all parent tables and perform the delete operation on these tables and also reset the identity column

  1. SET @TableName=''  
  2. WHILE EXISTS  
  3. (  
  4.             --Find all Parent tables  
  5.             SELECT T.table_name FROM INFORMATION_SCHEMA.TABLES T  
  6.             LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC  
  7.             ON T.table_name=TC.table_name  
  8.             WHERE TC.constraint_Type ='Primary Key'AND T.table_name <>'dtproperties' AND  
  9.             Table_type='BASE TABLE' AND T.table_name > @TableName  
  10. )  
  11. BEGIN  
  12.             SELECT @tableName=min(T.table_name) FROM INFORMATION_SCHEMA.TABLES T  
  13.             LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON T.table_name=TC.table_name  
  14.             WHERE TC.constraint_Type ='Primary Key' AND T.table_name <>'dtproperties' AND  
  15.             Table_type='BASE TABLE' AND T.table_name > @TableName  
  16.   
  17.             --DELETE the table  
  18.             EXEC('DELETE FROM '+ @tableName)  
  19.             PRINT 'DELETE FROM '+ @tableName  
  20.             --Reset identity column  
  21.             If EXISTS  
  22.             (  
  23.                         SELECT * FROM information_schema.columns  
  24.                         WHERE COLUMNPROPERTY(OBJECT_ID(QUOTENAME(table_schema)+'.'+  
  25.                         QUOTENAME(@tableName)), column_name,'IsIdentity')=1  
  26.             )  
  27.             BEGIN  
  28.                         DBCC CHECKIDENT (@tableName, RESEED, 1)  
  29.                         PRINT @tableName  
  30.             END  
  31. END 

Summary

Using the methoda described above, we can delete all the data from all the tables of a single database.

Up Next
    Ebook Download
    View all
    Learn
    View all