Friday, November 01, 2013

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

No comments:

Most Recent Post

Subscribe Blog via Email

Enter your email address:



Disclaimers:We have tried hard to provide accurate information, as a user, you agree that you bear sole responsibility for your own decisions to use any programs, documents, source code, tips, articles or any other information provided on this Blog.
Page copy protected against web site content infringement by Copyscape