SET IDENTITY_INSERT ON List In Database SQL Server

Question

I need to have a list that I can identify in my entire database

SET IDENTITY_INSERT ON

Thank you and thank you for helping me.

Closest I found is this: How do you check if IDENTITY_INSERT is set to ON or OFF in SQL Server? and Set IDENTITY_INSERT OFF for all tables. The first answer says that IDENTITY_INSERT is set per session, and will be OFF for a new session, so you don't really have to check it - you probably know if you set it to ON on the same session, or you can create a new session. — Kobi
– Kobi, Commented Jun 19, 2018 at 5:03
I accept this too, but I have to be sure to list and give it back. Thank you in any case. — MohammadBayat
– MohammadBayat, Commented Jun 19, 2018 at 5:24
Your entire question seems to be a single statement and not actually a question. I have no idea what you're actually asking about — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jun 19, 2018 at 6:14

Anagha · Accepted Answer · 2018-06-19 11:34:30Z

0

I think you need this:

SELECT   'SET IDENTITY_INSERT ' +OBJECT_NAME(OBJECT_ID)+ '  ON' 
FROM SYS.IDENTITY_COLUMNS 
WHERE OBJECT_NAME(OBJECT_ID) not like 'sqlagent_%'
AND OBJECT_NAME(OBJECT_ID) not like 'queue_messages%'

EDIT:

SELECT 'SET IDENTITY_INSERT ' +OBJECT_NAME(OBJECT_ID)+ '  ON;'+'SET IDENTITY_INSERT ' +OBJECT_NAME(OBJECT_ID)+ '  OFF;' 
FROM SYS.IDENTITY_COLUMNS 
WHERE OBJECT_NAME(OBJECT_ID) not like 'sqlagent_%'
AND OBJECT_NAME(OBJECT_ID) not like 'queue_messages%'

edited Jun 19, 2018 at 11:34

answered Jun 19, 2018 at 8:56

Anagha

9181 gold badge8 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MohammadBayat Over a year ago

The thing that actually does this is that I can identity I'll turn it on. That's fine thanks and grateful.

Anagha Over a year ago

If you want both statement together then you can check EDIT part.

Collectives™ on Stack Overflow

SET IDENTITY_INSERT ON List In Database SQL Server

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related