Category >
MSSQLSERVER
|| Published on :
Wednesday, August 27, 2014 || Views:
5669
||
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