In some situations, you may be turning the identity_insert property on and off for tables - for example, when doing an initial setup of data. Since only one table can have this turned on at a time (per session), here's a script to turn the identity_insert property off on all tables, so that you can turn it on for the table that you need.
This only works because SQL Server won't fail if the identity_insert is not turned on. However, it will fail when the table does not have an identity property set (this is why you can't just use sp_msforeachtable).
Please note that this is a quick script - use with caution and test first! A better way to do this would probably be just to turn the identity_insert off right after you use it.
Declare @NewLine char(1) ,@SQLStatement nvarchar(max) Set @NewLine = char(13) + char(10) -- procedures select @SQLStatement = isnull( @SQLStatement + @NewLine, '' ) + 'set identity_insert ' + object_name(object_id) + ' off' from SYS.IDENTITY_COLUMNS -- exec sp_executesql @SQLStatement Print @SQLStatement
