SQL 为所有表设置 IDENTITY_INSERT OFF
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10116759/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Set IDENTITY_INSERT OFF for all tables
提问by m_evangelista
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.
我有一个脚本，它创建一个完整的数据库并将所有记录插入到几十个表中。它工作得很好，除非在处理过程中出现一些问题，并且当脚本在插入过程中失败并且可以再次设置为 OFF 之前，表会留下 IDENTITY_INSERT ON。
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.
发生这种情况时，脚本在尝试再次运行时会自动失败，并在我们插入第一个表时出现错误“表 xx 的 IDENTITY_INSERT 已经打开”。
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.
作为故障保护，我想确保在运行安装脚本中的其余处理之前，所有表的 IDENTITY_INSERT 都设置为 OFF。
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.
作为替代方案，我们或许可以关闭 MS SQL 连接并再次打开它，据我所知，这将清除连接会话的所有 IDENTITY_INSERT 值。
What's the best way to do this, and prevent the "already on" errors?
什么是最好的方法来做到这一点，并防止“已经存在”的错误？
回答by John Dewey
Dynamic SQL:
动态 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)
Then copy & paste the resulting SQL into another query window and run
然后将生成的 SQL 复制并粘贴到另一个查询窗口中并运行
回答by Lynn Crumbling
EXEC sp_MSforeachtable @command1="SET IDENTITY_INSERT ? OFF"
回答by KevD
EXEC sp_MSforeachtable @command1="PRINT '?'; SET IDENTITY_INSERT ? OFF",
@whereand = ' AND EXISTS (SELECT 1 FROM sys.columns WHERE object_id = o.id AND is_identity = 1)'
Building on Lynn's answer, in case you're too lazy to perform this in more than one step - this should run on all tables where there is an identity column.
基于 Lynn 的回答，如果您懒得在多个步骤中执行此操作 - 这应该在所有有标识列的表上运行。
Caveat is only tested in 2012 and sp_MSforeachtableis of course entirely unsupported...
警告仅在 2012 年测试过，当然sp_MSforeachtable完全不受支持......
回答by Kumar Vaibhav
Building on @KevD's answer - It was working fine for disabling but here is more for enabling as well.
以@KevD 的回答为基础 - 禁用它工作正常，但这里还有更多用于启用。
To disable all identity inserts where they need to be disabled, use -
要禁用所有需要禁用的标识插入，请使用 -
EXEC sp_MSforeachtable @command1="PRINT '?'; SET IDENTITY_INSERT ? OFF",
@whereand = ' AND EXISTS (SELECT 1 FROM sys.columns WHERE object_id = o.id
AND is_identity = 1) and o.type = ''U'''
To enable all identity inserts where they need to be enabled, use -
要在需要启用的地方启用所有身份插入，请使用 -
EXEC sp_MSforeachtable @command1="PRINT '?'; SET IDENTITY_INSERT ? ON",
@whereand = ' AND EXISTS (SELECT 1 FROM sys.columns WHERE object_id = o.id
AND is_identity = 1) and o.type = ''U'''
Tested on Sql server 2014 and 2016
在 Sql server 2014 和 2016 上测试
回答by DBADon
I had a similar issue but I'd rather not use undocumented stored procedures in production. To automate this I built on to @John Dewey's answer and put it into a cursor. This iterates over 699 tables in 407 ms.
我有一个类似的问题，但我不想在生产中使用未记录的存储过程。为了自动执行此操作，我基于@John Dewey 的回答并将其放入游标中。这在 407 毫秒内迭代了 699 个表。
DECLARE @sql NVARCHAR(500) -- SQL command to execute
DECLARE sql_cursor CURSOR LOCAL FAST_FORWARD FOR
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)
OPEN sql_cursor
FETCH NEXT FROM sql_cursor INTO @sql
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE sp_executesql @sql --> Comment this out to test
-- PRINT @sql --> Uncomment to test or if logging is desired
FETCH NEXT FROM sql_cursor INTO @sql
END
CLOSE sql_cursor
DEALLOCATE sql_cursor
If you are against cursors, it could also be easily transformed into a while loop.
如果你反对游标，它也可以很容易地转换成一个 while 循环。
