Delete all the rows from all the tables in Ms-SQL Server

Category > MSSQLSERVER || Published on : Wednesday, August 27, 2014 || Views: 4795 || Delete all the rows Delete all the rows from all the tables in Ms-SQL Server


Here I will discuss you to show how we can do empty database structure, without having data in it.

so lets starts the learning. First fire the Ms-SQL Server Management Studio

Run following select statement which will generate set of delete statement to delete all the records for all the tables in your database.

SELECT
'Delete from ' + Table_Catalog + '.' + Table_Schema + '.' + Table_Name + ';'
FROM INFORMATION_SCHEMA.TABLES
WHERE Table_Type = 'BASE TABLE'
ORDER by TABLE_NAME


In case our database is large and you want to know status of which table is currently getting deleted you can use following:

SELECT
'Print(''Delete started for ' + Table_Catalog + '.' + Table_Schema + '.' + Table_Name + ''');' +
'Delete from ' + Table_Catalog + '.' + Table_Schema + '.' + Table_Name + ';' +
'Print(''Delete done for ' + Table_Catalog + '.' + Table_Schema + '.' + Table_Name + ''');'  +
'Print(''.............'');'
FROM INFORMATION_SCHEMA.TABLES
WHERE Table_Type = 'BASE TABLE'
ORDER by TABLE_NAME

so In this tutorial we have learned to how we can do empty database structure, without having data in it.

Download Source Codes