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

3 Comments | 3350 Views | Post by: Admin | Tags: 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.(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


Post Comments

if you have some suggestion, question, request on Delete all the rows from all the tables in Ms-SQL Server.

Please use the comment box to express your views.

 

Comments:

loading...