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

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.