SQL SERVER TUTORIAL IN DEPTH

The blog is to help users about sql server

Others

LightBlog

Breaking

Tuesday 29 July 2014

A nice way to truncate all the tables


I wanted to truncate all tables in a cursor. But I was unable to do it as some table were being reference by foreign key constraint.

This is the way I got it. 

1)Alter table with no check  constraint
2)Delete 
3)Alter table with Check Constraint
4) Reseed it

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
EXEC sp_MSForEachTable "DELETE FROM ?"
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
declare @table varchar(100)
declare NfrTable_cursor cursor
for
select TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo'
and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
order by TABLE_NAME
open NfrTable_cursor
fetch next from NfrTable_cursor into @table
WHILE @@FETCH_STATUS = 0
BEGIN
 DBCC CHECKIDENT (@table, reseed, 0); 
fetch next from NfrTable_cursor into @table
end
close NfrTable_cursor
deallocate NfrTable_cursor

No comments:

Post a Comment

 test