我想截断游标中的所有表。但我无法做到这一点，因为某些表正在被外键约束引用

问题描述

declare @table varchar(100)
declare NfrTable_cursor cursor
for
select  tableDetails from #temporaryTable
open NfrTable_cursor
fetch next from NfrTable_cursor into @table
WHILE @@FETCH_STATUS = 0
BEGIN

exec('select top 1 1 as found from ' + @table)
If @@RowCount > 0
begin
exec ('truncate table ' + @table)
end
fetch next from NfrTable_cursor into @table
end
close NfrTable_cursor
deallocate NfrTable_cursor

我收到的错误是：

I am getting error as :

"Cannot truncate table 'BragDetail' because it is being referenced by a FOREIGN KEY constraint."

- 如何在表格名称为动态的游标内实现此目的。

- 我引用了一些网站，但它建议删除约束，截断并重新创建约束..但我不知道如何在游标内做。请帮帮我。

--How can I achieve this inside a cursor where the table name is dynamic.
-- I referred some sites,,but it is suggesting to drop the constraint,truncate and recreate the constraint.. but I am not sure how to do inside a cursor. Please help me in this.

推荐答案

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

这篇关于我想截断游标中的所有表。但我无法做到这一点，因为某些表正在被外键约束引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！