1

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.

DhruvJoshi
  • 17,041
  • 6
  • 41
  • 60
MohammadBayat
  • 13
  • 4
  • 1
    Closest I found is this: [How do you check if IDENTITY_INSERT is set to ON or OFF in SQL Server?](https://stackoverflow.com/q/10637976/7586) and [Set IDENTITY_INSERT OFF for all tables](https://stackoverflow.com/q/16044046/7586). 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 Jun 19 '18 at 05:03
  • I accept this too, but I have to be sure to list and give it back. Thank you in any case. – MohammadBayat Jun 19 '18 at 05:24
  • 1
    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 Jun 19 '18 at 06:14
  • Nice trick with try and catch [here](https://stackoverflow.com/a/47700982/1683270) – ahmed abdelqader Jun 19 '18 at 06:16
  • Thank you very much. This page is a great help – MohammadBayat Jun 19 '18 at 07:37

1 Answers1

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%'
Anagha
  • 918
  • 1
  • 8
  • 17