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 NULL) AND
  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 NULL) AND
  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
  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.