Как проверить, если Identity INSERT имеет значение вкл или выкл в SQL Server?

в итоге:

• решение Натана является самым быстрым:

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

  • при использовании оболочки API можно уменьшить всю проверку до просто проверки строк. Например, при использовании C# ' s SqlDataReaders свойства HasRows и запрос построить так:

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

• решение Рикардо обеспечивает большую гибкость, но требует идентичности столбца имя

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

• Богдан Боданов решение, используя try/catch также будет работать, но дополнительная проверка должна ограничивать обработку исключений случаями IDENTITY_INSERT is already ON for table 'MyTable'. Cannot perform SET operation for table 'MyTable';

Если вы пытаетесь отключить IDENTITY_INSERT для какой-либо другой таблицы, чтобы избежать получения ошибки, когда вы хотите установить IDENTITY_INSERT on, следующее также может работать для вас. Как и другие сказали в этом потоке IDENTITY_INSERT-это параметр сеанса без прямой видимости. Однако я сделал интересное открытие, что SET IDENTITY_INSERT OFF не выдает ошибку для любой таблицы, которая имеет идентификатор, независимо от того, включен ли IDENTITY_INSERT для этой таблицы. Так что мне пришло в голову, что я могу просто вызовите SET IDENTITY_INSERT ... Для каждой таблицы с идентификатором в базе данных. Это немного похоже на решение грубой силы, но Я обнаружил, что следующий динамический блок SQL сделал трюк очень хорошо.

---- 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

Если вы хотите узнать о переменной сеанса... Хороший вопрос, но я не вижу, где эта информация будет полезна. В обычном исполнении, чтобы проверить нормальный ответ таблицы на вставку, это должно работать!

-- Если вы только хотите знать, есть ли Identity insert в данной таблице:

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

-- или... Используйте это, если вы хотите выполнить что-то в зависимости от результата:

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

удачи!

вы можете узнать, включен ли 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 

очень хороший вопрос. У меня такая же проблема. Может быть, вы можете попробовать сбросить IDENTITY_INSERT с помощью TRY/CATCH? Например, вы делаете задание, но не уверены, что задание завершено и IDENTITY_INSERT имеет значение OFF.

почему бы вам не попробовать:

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

также я не уверен, что это работает правильно, но я вижу, что добавление только SET IDENTITY_INSERT ... OFF не возвращает ошибку. Так что вы можете установить на всякий случай в конце SET IDENTITY_INSERT ... OFF.

вы также можете использовать метод ObjectProperty, чтобы определить, имеет ли таблица идентификатор:

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