SQL Server에서 IDENTITY_INSERT가 ON 또는 OFF로 설정되어 있는지 어떻게 확인합니까?

SQL Server에서 IDENTITY_INSERT가 ON 또는 OFF로 설정되어 있는지 어떻게 확인합니까?

---

나는 이것을 검색했지만 그것이 나타난 스레드는 질문을 이해하지 못하는 사람들로부터 답변을받는 경향이 있었다.

다음 구문을 사용하십시오.

SET IDENTITY_INSERT Table1 ON

다음과 같이 어떻게하면 되나요?

GET IDENTITY_INSERT Table1

이 정보를 얻기 위해 데이터베이스의 데이터 나 설정에 대해 아무것도하고 싶지 않습니다. 감사!

---

SET IDENTITY_INSERT세션에 민감 하므로 어딘가에 저장하지 않고 버퍼 수준에서 관리합니다. 이는 IDENTITY_INSERT현재 세션에서이 키워드를 사용 하지 않으므로 상태 를 확인할 필요가 없음을 의미 합니다.

죄송합니다. 도움이되지 않습니다.

그래도 좋은 질문 :)

출처 : 여기

업데이트 내가 링크 한 사이트 IMO에서도 볼 수있는 방법이있을 수 있습니다. 유용하기에는 너무 많은 노력이 필요합니다.

if

(select max(id) from MyTable) < (select max(id) from inserted)

--Then you may be inserting a record normally

BEGIN
    set @I = 1 --SQL wants something to happen in the "IF" side of an IF/ELSE
END

ELSE --You definitely have IDENTITY_INSERT on.  Done as ELSE instead of the other way around so that if there is no inserted table, it will run anyway


BEGIN
.... Code that shouldn't run with IDENTITY_INSERT on
END

---

요약해서 말하자면:

• Nathan의 솔루션 이 가장 빠릅니다.

  SELECT OBJECTPROPERTY(OBJECT_ID('MyTable'), 'TableHasIdentity');
  

  • API 래퍼를 사용할 때 전체 검사를 행만 검사하는 것으로 줄일 수 있습니다. 예를 들어 C #의 SqlDataReaders속성 HasRows과 다음과 같은 쿼리 구문을 사용할 때 :

    SELECT CASE OBJECTPROPERTY(OBJECT_ID('MyTable'), 'TableHasIdentity')
           WHEN 1 THEN '1' ELSE NULL END
    

• Ricardo의 솔루션 은 더 많은 유연성을 허용하지만 컬럼의 ID 이름이 필요합니다.

  SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('MyTable', 'U') 
                          AND name = 'MyTableIdentityColumnName';
  

• try/를 사용하는 Bogdan Bodanov 솔루션도 catch작동하지만 추가 검사는 예외 처리를 다음과 같은 경우로 제한해야합니다.IDENTITY_INSERT is already ON for table 'MyTable'. Cannot perform SET operation for table 'MyTable';

---

아래 코드를 사용하여 identity_insert가 켜져 있는지 여부와 해당 테이블에 대해 확인할 수 있습니다.

declare @tableWithIdentity varchar(max) = '';
SET IDENTITY_INSERT ExampleTable ON

begin try
  create table #identityCheck (id int identity(1,1))
  SET IDENTITY_INSERT #identityCheck ON
  drop table #identityCheck
end try
begin catch
  declare @msg varchar(max) = error_message()
  set @tableWithIdentity= @msg;
  set @tableWithIdentity = 
  SUBSTRING(@tableWithIdentity,charindex('''',@tableWithIdentity,1)+1, 10000)

  set @tableWithIdentity = SUBSTRING(@tableWithIdentity,1, charindex('''',@tableWithIdentity,1)-1)

  print @msg;
  drop table #identityCheck
end catch

if @tableWithIdentity<>''
begin
  print ('Name of table with Identity_Insert set to ON: ' + @tableWithIdentity)
end
else
begin
  print 'No table currently has Identity Insert Set to ON'
end 

---

If you're attempting to turn off IDENTITY_INSERT for some other table to avoid getting an error when you want to set IDENTITY_INSERT on, the following may also work for you. As other have said on this thread IDENTITY_INSERT is a session setting with no direct visibility. However I made the interesting discovery that SET IDENTITY_INSERT OFF doesn't error out for any table that has an identity whether or not IDENTITY_INSERT is ON for that table. So it occurred to me that I could just call SET IDENTITY_INSERT ... OFF for every table with an identity in the database. It feels a bit like a brute force solution, but I found that the following dynamic SQL block did the trick very nicely.

---- make sure IDENTITY_INSERT is OFF ----
DECLARE @cmd NVARCHAR(MAX)
SET @cmd = CAST((SELECT 'SET IDENTITY_INSERT ' + 
             QUOTENAME(OBJECT_SCHEMA_NAME(t.object_id)) + '.' + 
             QUOTENAME(t.name) + ' OFF' + CHAR(10)
             FROM sys.columns c 
             JOIN sys.tables t ON t.object_id = c.object_id
             WHERE c.is_identity = 1 
             ORDER BY 1 FOR XML PATH('')) AS NVARCHAR(MAX))
EXEC sp_executesql @cmd

---

If you want to know about the session variable... Good question, but I cant see where this information would be usefull. In normal execution to check a normal table response to an insert, this should work!

-- If you only want to know if there is identity insert on a given table:

select is_identity
from sys.columns
where object_id = OBJECT_ID('MyTable', 'U') and name = 'column_Name'

-- Or... Use this if you want to execute something depending on the result:

if exists (select *
from sys.columns
where object_id = OBJECT_ID('MyTable', 'U') and is_identity = 1)
... your code considering identity insert
else
... code that should not run with identity insert

Have fun!

---

Very good question. I Have same issue. May be you can try to reset IDENTITY_INSERT using TRY/CATCH? For example, you make the job but not sure if the job is finished and IDENTITY_INSERT is set to OFF.

Why you don't try:

BEGIN TRY 
...
END TRY 
BEGIN CATCH
SET IDENTITY_INSERT table OFF;
END CATCH;

Also I am not sure that this is working correctly but I see that adding only SET IDENTITY_INSERT ... OFF did not return error. So you can set just in case in the end SET IDENTITY_INSERT ... OFF.

---

you can also use the ObjectProperty method to determine if a table has an identity:

DECLARE @MyTableName nvarchar(200)
SET @MyTableName = 'TestTable'
SELECT CASE OBJECTPROPERTY(OBJECT_ID(@MyTableName), 'TableHasIdentity') 
WHEN 1 THEN 'has identity' 
ELSE 'no identity columns' 
END as HasIdentity

참고URL : https://stackoverflow.com/questions/10637976/how-do-you-check-if-identity-insert-is-set-to-on-or-off-in-sql-server