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