Set IDENTITY_INSERT OFF for all tables

Question

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.

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.

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.

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?

Answer 1

Dynamic 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