sql - 为所有表设置 IDENTITY_INSERT OFF

Question

我有一个脚本，可以创建整个数据库并将所有记录插入到几十个表中。它工作得很好，除非在处理过程中出现一些问题，并且当脚本在插入期间失败且在再次设置为 OFF 之前，表会保持 IDENTITY_INSERT ON。

发生这种情况时，脚本在尝试再次运行时会自动失败，当我们进入第一个表的插入时，会出现错误“IDENTITY_INSERT is already ON for table xx”。

作为故障保护，我想确保在安装脚本中运行其余处理之前，所有表的 IDENTITY_INSERT 设置为 OFF。

作为替代方案，我们也许可以关闭 MS SQL 连接并再次打开它，据我所知，这将清除连接 session 的所有 IDENTITY_INSERT 值。

执行此操作并防止“已开启”错误的最佳方法是什么？

Answer 1

动态 SQL:

select 'set identity_insert ['+s.name+'].['+o.name+'] off'
from sys.objects o
inner join sys.schemas s on s.schema_id=o.schema_id
where o.[type]='U'
and exists(select 1 from sys.columns where object_id=o.object_id and is_identity=1)

然后将生成的 SQL 复制并粘贴到另一个查询窗口中并运行