By using below script we can
able to delete all tables data in SQL SERVER without having any issues. In the script
I have followed below steps
·
Disable all
constraints on all tables. Due to that we can delete all tables without having
any constraints issues by using system stored procedure sp_MSForEachTable
·
Disable all TRIGGERs
on all tables
·
Delete all tables
data
·
If any tables having
identity, we need to set identity to 0
·
Once we delete all
tables data , we will enable all CONSTRAINTS
·
Once we delete all
tables data , we will enable all TRIGGERs
Cautious
Before you execute this script
and always use statement to ensure that your executing in correct database
EXEC SP_MSFOREACHTABLE 'ALTER TABLE ? NOCHECK
CONSTRAINT ALL'
EXEC SP_MSFOREACHTABLE 'ALTER TABLE ? DISABLE
TRIGGER ALL'
EXEC SP_MSFOREACHTABLE 'DELETE FROM ?'
EXEC SP_MSFOREACHTABLE 'IF
OBJECTPROPERTY(OBJECT_ID(''?''), ''TABLEHASIDENTITY'') = 1 DBCC CHECKIDENT
(''?'', RESEED, 0)'
EXEC SP_MSFOREACHTABLE 'ALTER TABLE ? CHECK
CONSTRAINT ALL'
EXEC SP_MSFOREACHTABLE 'ALTER TABLE ? ENABLE
TRIGGER ALL'
Kiran Reddy A
Kiran has been a part of the IT industry for more than six years and Involved in many MSBI projects and product development with extensive usage of ETL and Reporting tools like SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), SQL Server Reporting Service (SSRS) and T-SQL
Get Free Email Updates to your Inbox!
