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.

SQL Server DBA Training from Beginner to Advanced

Delete All Table Data Within a Database in SQL Server

Login with your site account

Register a new account

Modal title