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.

Maltreat

See also How do you determine what SQL Tables have an identity column programatically – Emmieemmit

Answer 1

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

ObjectProperty is available starting sql server 2008 Reference: OBJECTPROPERTY

Clapperclaw

Warning OBJECTPROPERTY works only if executed from the database where object is located. – Vilmavim

Answer 2

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.

Peremptory

please Can u elaborate on this query? Thank u – Maltreat

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

Answer 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

Hitherward

The doco states "Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.": learn.microsoft.com/en-us/sql/relational-databases/… – Futures

Answer 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'
    )

Alphard

Answer 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')

Nuke

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

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. – Deter

Answer 6

One way to do this would be to make use of the stored procedure sp_help. I.e:

sp_help MyTable

This will return a DataSet that has all the information you would need on the table. There is a specific Table that has information on identities.

I.e:

If it does not contain an identity field, the Identity column will say: "No identity column defined".

Oceanid

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. – Bibbs

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

Answer 7

@Pranay: he said Compact Edition. Stored procedures aren't supported, and there is no sys.anything.

This is the call:

SELECT Count(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE AUTOINC_INCREMENT IS NOT NULL AND TABLE_NAME='this_table'

It will return either 1 (true) or 0 (false).

Inositol

doesn't work for me - no column "AUTO_INC_INCREMENT" – Lexicologist

You have an extra underscore – Inositol

I don’t see an AUTOINC_INCREMENT in SQL Server 11.0.7001. – Redfaced

So this is specific to CE? Ah. – Redfaced

Answer 8

... declare @tblhasIdentCol bit = IF (IDENT_CURRENT( @dbName +'.'+ @schemaName +'.'+ @tableName ) IS NOT NULL , 1 , 0 )

You get numeric value if table has identity

Moat

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

Answer 9

Very simple answer would be to run this:

SELECT IDENT_CURRENT('TABLE-NAME')

This would give max value of identity column if exists, if the column doesn't exist, it gives 1 as result.

Based on max value, you can identify which column is having that and determine the identity column.

Lackey

Answer 10

This the query that get u all the tableNames, columnnames of the table, and is_identity or not in the selected database

SELECT
     sys.columns.name
   , sys.tables.name
   , is_identity
FROM sys.columns
INNER JOIN sys.tables ON sys.tables.object_id = sys.columns.object_id
    AND sys.columns.is_identity = 1

Flaming

Answer 11

CREATE FUNCTION dbo.fnTableHasIdentity(@Tbl sysname)
RETURNS TINYINT
BEGIN 
  RETURN OBJECTPROPERTY(OBJECT_ID(@Tbl), 'TableHasIdentity')
END 

--As simple as that!

Forseti

Answer 12

select t.name as TableName,c.name as ColumnName
from sys.identity_columns c
inner join sys.tables t on c.object_id = t.object_id
where t.name = 'TableName'

Addax

Answer 13

If you like me, needed to be able to do this for tables in an arbitrary database, then I found the following solution:

IF EXISTS (
    SELECT 1
    FROM [database name].sys.identity_columns AS id_col
    INNER JOIN [database name].sys.objects
        ON objects.object_id = id_col.object_id
    INNER JOIN [database name].sys.schemas
        ON schemas.schema_id = objects.schema_id
        AND schemas.name = 'schema name'
    WHERE OBJECT_NAME(id_col.object_id, DB_ID('database name')) = 'table name'
) SELECT 1 ELSE SELECT 0

Pedalfer

Answer 14

you can get the 1 or 0 Boolean Form if the current table has identity Columns by using this

SELECT Count(Column_ID) FROM sys.identity_columns WHERE OBJECT_NAME(object_id) = 'tableName'

Tufts

Answer 15

One way to list all Tables with their identity column if it exists to get you desired table add at the end of the filter "and o.name='TableName'" where Tbale Nam is the table you are looking for

SELECT o.[Name][TableName],i.[name][IdentityColName] FROM 
sys.objects o 
left outer join sys.identity_columns i on i.object_id=o.object_id
where o.type='U'

Cortez

Answer 16

One way to do this.

IF NOT EXISTS (SELECT * FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID('YOURTABLENAME') AND is_identity = 1) BEGIN
    -- your code here
END

Cheeseburger