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

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

Step 2:

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

EXEC sys.sp_msforeachtable 'DELETE FROM ?'

Step 3:

Enable all constrains on the database

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.

 DECLARE @tableName VARCHAR(200)  
SET @tableName=''  
WHILE EXISTS  
 (  
 --Find all child tables AND those which have no relations  
             SELECT T.table_name FROM INFORMATION_SCHEMA.TABLES T  
             LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC  
             ON T.table_name=TC.table_name  
             WHERE (TC.constraint_Type ='Foreign Key'or TC.constraint_Type IS NULL) AND  
             T.table_name NOT IN ('dtproperties','sysconstraints','syssegments')AND  
             Table_type='BASE TABLE' AND T.table_name > @TableName  
 )  
 BEGIN  
             SELECT @tableName=min(T.table_name) FROM INFORMATION_SCHEMA.TABLES T  
             LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC  
             ON T.table_name=TC.table_name  
             WHERE (TC.constraint_Type ='Foreign Key'or TC.constraint_Type IS NULL) AND  
             T.table_name NOT IN ('dtproperties','sysconstraints','syssegments') AND  
             Table_type='BASE TABLE' AND T.table_name > @TableName  
             --Truncate the table  
             EXEC('DELETE FROM '+@tablename)  
     PRINT 'DELETE FROM '+@tablename  
 END

Step 2:

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

SET @TableName=''  
WHILE EXISTS  
(  
            --Find all Parent tables  
            SELECT T.table_name FROM INFORMATION_SCHEMA.TABLES T  
            LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC  
            ON T.table_name=TC.table_name  
            WHERE TC.constraint_Type ='Primary Key'AND T.table_name <>'dtproperties' AND  
            Table_type='BASE TABLE' AND T.table_name > @TableName  
)  
BEGIN  
            SELECT @tableName=min(T.table_name) FROM INFORMATION_SCHEMA.TABLES T  
            LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON T.table_name=TC.table_name  
            WHERE TC.constraint_Type ='Primary Key' AND T.table_name <>'dtproperties' AND  
            Table_type='BASE TABLE' AND T.table_name > @TableName  
  
            --DELETE the table  
            EXEC('DELETE FROM '+ @tableName)  
            PRINT 'DELETE FROM '+ @tableName  
            --Reset identity column  
            If EXISTS  
            (  
                        SELECT * FROM information_schema.columns  
                        WHERE COLUMNPROPERTY(OBJECT_ID(QUOTENAME(table_schema)+'.'+  
                        QUOTENAME(@tableName)), column_name,'IsIdentity')=1  
            )  
            BEGIN  
                        DBCC CHECKIDENT (@tableName, RESEED, 1)  
                        PRINT @tableName  
            END  
END

Summary

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