I've searched for this, but threads in which it appeared tended to have answers from people who didn't understand the question.
Take the following syntax:
SET IDENTITY_INSERT Table1 ON
How do you do something more like this:
GET IDENTITY_INSERT Table1
I don't want to do anything whatsoever to the data in the database or to the settings to get this information though. Thanks!
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.
Great question though :)
Source: Here
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
In summary:
Nathan's solution is the fastest:
SELECT OBJECTPROPERTY(OBJECT_ID('MyTable'), 'TableHasIdentity');
when using an API wrapper, one can reduce the entire check to just checking for rows. For instance when using C#'s SqlDataReaders property HasRows and a query construct like:
SELECT CASE OBJECTPROPERTY(OBJECT_ID('MyTable'), 'TableHasIdentity')
WHEN 1 THEN '1' ELSE NULL END
Ricardo's solution allows more flexibility but requires the Column's identity name
SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('MyTable', 'U')
AND name = 'MyTableIdentityColumnName';
Bogdan Bodanov solution, using try/catch would work as well, but additional checking should confine exception handling to cases of IDENTITY_INSERT is already ON for table 'MyTable'. Cannot perform SET operation for table 'MyTable';
You can discover whether or not identity_insert is on, and if so for what table using the code below.
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
IDENTITY_INSERT on was made within the calling script (or prior), there's most certainly a reason for it. :p Assuming that the caller won't keep relying on the info thereafter is wrong and potentially hazardous. Design wise, it would be best IMHO to simply try setting it on anyway and let the whole thing crash and burn if it must. At least devs will have a clue on what went wrong - assuming errors are logged somewhere. –
Sculptor 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.
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!
IDENTITY_INSERT is on not if the table has an identity column –
Lachrymatory is_identity column in the sys.columns table identifies whether that column is an identity column or not, not whether the variable IDENTITY_INSERT is ON or OFF –
Flute Here is my solution. It is very similar to @jmoreno's answer.
You would call it like this
DECLARE @IdentityInsert VARCHAR(20)
EXEC dbo.GetIdentityInsert 'YourDb', 'YourSchema', 'YourTable', @IdentityInsert OUT
SELECT @IdentityInsert
This returns a 1-row recordset with column name IDENTITY_INSERT, that can be either ON, OFF, or NO_IDENTITY (if the given table doesn't have an identity column). It also sets the output parameter @IdentityInsert. So you can adjust the code to whichever method you prefer.
It would be nice to get this into a user-defined function, but unfortunately I couldn't find a way to avoid the TRY..CATCH block, which you cannot use in user-defined functions.
-- ================================================================================
-- 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
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
TableHasIdentity value as defined on the table, not the actual session-stored IDENTITY_INSERT value, which is what the op is looking for. Try for yourself: have a query that checks for the property, then alter the IDENTITY_INSERT value for the table and then get the object property again. Its value will still be the same as it was before. –
Sculptor © 2022 - 2024 — McMap. All rights reserved.
IDENTITY_INSERTis off! If you're not clear whether something stays the same session, google sessions (not exactly = connections), check outsys.dm_exec_sessionsandsys.dm_exec_connections, or downloadsp_WhoIsActiveandEXEC sp_WhoIsActive @show_sleeping_spids = 2, @show_own_spid = 1– Turanian