How to identify whether the table has identity column
This query returns a table's identity column name:
CREATE PROCEDURE dbo.usp_GetIdentity@schemaname nvarchar(128) = 'dbo' ,@tablename nvarchar(128)ASBEGIN SELECT OBJECT_NAME(OBJECT_ID) AS TABLENAME, NAME AS COLUMNNAME, SEED_VALUE, INCREMENT_VALUE, LAST_VALUE, IS_NOT_FOR_REPLICATION FROM SYS.IDENTITY_COLUMNS WHERE OBJECT_NAME(OBJECT_ID) = @tablename AND OBJECT_SCHEMA_NAME(object_id) = @schemanameENDThen form the code side.
Call this stored procedure using the datareader role, then check datareader.hasrows(). If the condition value is true (1), then the table has identity column if set. If not then it doesn't have an identity column.
IF (OBJECTPROPERTY(OBJECT_ID('TABLE_NAME'), 'TableHasIdentity') = 1) ObjectProperty is available starting sql server 2008 Reference: OBJECTPROPERTY
I know it's long time ago but i found this helpful
try this :
IF EXISTS (SELECT * from syscolumns where id = Object_ID(@TABLE_NAME) and colstat & 1 = 1)BEGIN -- Do your thingsEND