Delete all the rows from all the tables in SQL Server

If you are in situation where you just want empty database structure, without having data in it.

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 your 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