Question

我对此进行了探讨,但似乎在其中得到不了解问题的人们的答复。

2. 采取下列措施:

SET IDENTITY_INSERT Table1 ON

你如何做这样的事情:

GET IDENTITY_INSERT Table1

我不想对数据库中的数据或环境做任何事情,以便获取这些信息。感谢!

Answer 1

Since SET IDENTITY_INSERT is a session sensitive, it is managed in buffer level without storing somewhere. This means we do not need to check the IDENTITY_INSERT status as we never use this key word in current session.

Sorry, no help for this.

......

资料来源:。

Update There are ways maybe to do this, also seen in the site I linked, IMO, it is too much effort to be useful.

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

Answer 2

概述:

Nathan s Solutions是最快的:
```
SELECT OBJECTPROPERTY(OBJECT_ID( MyTable ),  TableHasIdentity );
```
- 在使用AAP包时,可以减少整个检查,只检查行。例如,在使用C# s SqlDataReaders 财产<代码>HasRow时,以及像以下一样的问询:
```
SELECT CASE OBJECTPROPERTY(OBJECT_ID( MyTable ),  TableHasIdentity )
       WHEN 1 THEN  1  ELSE NULL END
```

Ricardo s Solutions 允许更大的灵活性,但要求用哥伦格语标明身份

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

Bogdan Bodanov Solutions, using try />fish 还将工作,但额外检查应限于处理<代码>IDENTability_INSERT已上表MyTable 的案件。表MyTable ;

Answer 3

您可以发现身份认同是否已经存在,如果是,如何使用以下代码。

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

Answer 4

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

Answer 5

非常好的问题。我有同样的问题。您可以尝试重新确定身份认同。 INSERT using TRY/CATCH? 例如,你做的是工作,但并不确定工作是否完成以及身份证明。 INSERT被安排到法国统计局。

为什么你不尝试:

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

我也不清楚这项工作是正确的,但我看到,只增加<条码>。 SET IDENTITY INSERT ...... OFF没有回差错。因此,你可以在结尾的<代码>上确定。 SET IDENTITY INSERT ...... OFF。

Answer 6

如果你想要知道本届会议的情况会变...... 良好问题,但我看看,这一信息在什么地方有用。在正常执行过程中,为了检查对插入的正常表反应,这应当奏效!

纽约总部如果你只想知道某个表格中是否有身份:

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

页: 1 如果你想根据结果执行某些事情的话:

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

!!

Answer 7

这就是我的解决办法。这与@jmoreno的答复非常相似。

请将此称为我们。

DECLARE @IdentityInsert VARCHAR(20)
EXEC dbo.GetIdentityInsert  YourDb ,  YourSchema ,  YourTable , @IdentityInsert OUT
SELECT @IdentityInsert

该表以“IDENTITY-ERTINS”一栏记录,该栏可以是:ONF,也可以是NO_IDENTITY(如果该表没有身份栏)。它还规定了产出参数@IdentityInsert。因此,你可以按照你所希望的哪一种方法调整法典。

把它变成一个用户界定的职能是明智的,但不幸的是,我找不到避免危险的方法。 CATCH组,您无法在用户界定的职能中使用。

-- ================================================================================
-- Check whether the table specified has its IDENTITY_INSERT set to ON or OFF.
-- If the table does not have an identity column, NO_IDENTITY is returned.
-- Tested on SQL 2008.
-- ================================================================================
CREATE PROCEDURE dbo.GetIdentityInsert

      @dbname sysname
    , @schemaname sysname
    , @table sysname
    , @IdentityInsert VARCHAR(20) OUTPUT

AS

BEGIN

    SET NOCOUNT ON

    DECLARE @OtherTable nvarchar(max)
    DECLARE @DbSchemaTable nvarchar(max)

    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;
    DECLARE @ErrorNumber INT;
    DECLARE @object_id INT;

    SET @DbSchemaTable = @dbname +  .  + @schemaname +  .  + @table

    SET @object_id = OBJECT_ID(@DbSchemaTable)
    IF @object_id IS NULL
    BEGIN
        RAISERROR( table %s doesn  t exist , 16, 1, @DbSchemaTable)
        RETURN
    END


    BEGIN TRY

        SET @object_id = OBJECT_ID(@DbSchemaTable)

        IF OBJECTPROPERTY(@object_id, TableHasIdentity ) = 0
        BEGIN
            SET @IdentityInsert =  NO_IDENTITY 
        END
        ELSE
        BEGIN
            -- Attempt to set IDENTITY_INSERT on a temp table. This will fail if any other table
            -- has IDENTITY_INSERT set to ON, and we ll process that in the CATCH
            CREATE TABLE #GetIdentityInsert(ID INT IDENTITY)
            SET IDENTITY_INSERT #GetIdentityInsert ON
            SET IDENTITY_INSERT #GetIdentityInsert OFF
            DROP TABLE #GetIdentityInsert

            -- It didn t fail, so IDENTITY_INSERT on @table must set to OFF
            SET @IdentityInsert =  OFF 
        END
    END TRY


    BEGIN CATCH

        SELECT 
            @ErrorMessage = ERROR_MESSAGE(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE(),
            @ErrorNumber = ERROR_NUMBER();

        IF @ErrorNumber = 8107  --IDENTITY_INSERT is already set on a table
        BEGIN
            SET @OtherTable = SUBSTRING(@ErrorMessage, CHARINDEX(char(39), @ErrorMessage)+1, 2000)
            SET @OtherTable = SUBSTRING(@OtherTable, 1, CHARINDEX(char(39), @OtherTable)-1)

            IF @OtherTable = @DbSchemaTable 
            BEGIN
                -- If the table name is the same, then IDENTITY_INSERT on @table must be ON
                SET @IdentityInsert =  ON 
            END
            ELSE
            BEGIN
                -- If the table name is different, then IDENTITY_INSERT on @table must be OFF
                SET @IdentityInsert =   OFF 
            END
        END
        ELSE
        BEGIN
            RAISERROR (@ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState);
            --THROW     Use this if SQL 2012 or higher
        END

    END CATCH

    SELECT [IDENTITY_INSERT] = @IdentityInsert
END
GO

Answer 8

您也可以使用客观方法确定表格是否具有特性:

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

友情链接