How to identify whether the table has identity column

Question

I want to find out whether the table has an identity column or not. Table is unknown to me. I have not done the structure of the table. Using Query?

I am using Sql Server Compact Edition.

Solution 1

This query returns a table's identity column name:

CREATE PROCEDURE dbo.usp_GetIdentity
@schemaname nvarchar(128) = 'dbo'  
,@tablename nvarchar(128)
AS
BEGIN
    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) = @schemaname
END

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

Solution 2

IF (OBJECTPROPERTY(OBJECT_ID('TABLE_NAME'), 'TableHasIdentity') = 1) 

ObjectProperty is available starting sql server 2008 Reference: OBJECTPROPERTY

Solution 3

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

Solution 4

Any of the below queries can be used to check if an Identity Column is present in the table

1)

SELECT *
FROM sys.identity_columns
WHERE OBJECT_NAME(object_id) = 'TableName'

2)

SELECT *
FROM sys.identity_columns
WHERE object_id = (
        SELECT id
        FROM sysobjects
        WHERE name = 'TableName'
    )

Solution 5

I would just like to add this option as well as I think it is the simplest

SELECT COLUMNPROPERTY(OBJECT_ID('TableName'),'ColumnName','isidentity')

View more solutions

Comments

[Shiny]

please Can u elaborate on this query? Thank u

[Rajat]

I agree with @johnmcp. This option is indeed simple and returns 1 when column is identity and 0 when column is not an identity column

[Mike Cheel]

I'll add that you can highlight the name of the table and then use keyboard combo ALT+F1 as short cut for this when in SSMS.

[XDS]

If you are really paranoid you may also want to use: WHERE OBJECT_NAME(OBJECT_ID) = @ tablename AND OBJECT_SCHEMA_NAME(OBJECT_ID) = @ schema

[Philip Stratford]

But it doesn't answer the OP's (and my) question, which is how to determine whether a given table has an identity column, not whether a given column is designated as the identity column. The whole point is that the table may have no identity column.

[reggaeguitar]

@MikeCheel doesn't work for me in ssms version 17.9.1

[Belladonna]

This worked perfectly for me, thank you (SQL Svr 2017)