为所有表设置 IDENTITY_INSERT OFF [英] Set IDENTITY_INSERT OFF for all tables

问题描述

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

I have a script which creates an entire database and inserts all records to a few dozen tables. It works great, unless there is some issue during the processing, and a table gets left with IDENTITY_INSERT ON, when the script fails during insertion and before it can be set to OFF again.

如果发生这种情况，脚本在尝试再次运行时会自动失败，并在我们插入第一个表时出现错误IDENTITY_INSERT 已为表 xx 开启".

When this happens, the script automatically fails when attempting to run it again, with the error "IDENTITY_INSERT is already ON for table xx" as we go into the insertion for the first table.

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

As a failsafe I would like to make sure that IDENTITY_INSERT is set to OFF for all tables, before running the rest of the processing in the setup script.

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

As an alternative, we could perhaps close the MS SQL connection and open it again, which, as I understand it, would clear all IDENTITY_INSERT values for the connection session.

什么是最好的方法来做到这一点，并防止已经发生"的错误?

What's the best way to do this, and prevent the "already on" errors?

推荐答案

动态 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 粘贴到另一个查询窗口中并运行

Then copy & paste the resulting SQL into another query window and run

这篇关于为所有表设置 IDENTITY_INSERT OFF的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！