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.

See also [How do you determine what SQL Tables have an identity column programatically](http://stackoverflow.com/q/87747) — Michael Freidgeim, Jun 10 '16 at 08:47

score 37 · Answer 1 · edited Dec 01 '17 at 12:56

37

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

ObjectProperty is available starting sql server 2008 Reference: OBJECTPROPERTY

edited Dec 01 '17 at 12:56

DiskJunky

4,750
3
37
66

answered Sep 20 '16 at 12:09

h3n

431
5
2

Warning OBJECTPROPERTY works only if executed from the database where object is located. – Muflix Sep 23 '22 at 21:32

score 31 · Answer 2 · edited Jan 04 '20 at 00:42

31

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.

edited Jan 04 '20 at 00:42

katzbatz

332
2
16

answered May 20 '10 at 07:41

Pranay Rana

175,020
35
237
263

please Can u elaborate on this query? Thank u – Shiny May 20 '10 at 08:35
If you are really paranoid you may also want to use: WHERE OBJECT_NAME(OBJECT_ID) = @ tablename AND OBJECT_SCHEMA_NAME(OBJECT_ID) = @ schema – XDS Jun 05 '18 at 15:59

score 19 · Answer 3 · answered May 07 '14 at 10:36

19

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

answered May 07 '14 at 10:36

Wahid Bitar

13,776
13
78
106

score 15 · Answer 4 · edited Apr 20 '16 at 16:13

15

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

edited Apr 20 '16 at 16:13

Devart

119,203
23
166
186

answered Apr 09 '13 at 07:09

Bhavneet

151
1
2

score 7 · Answer 5 · answered Dec 14 '16 at 16:58

7

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

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

answered Dec 14 '16 at 16:58

johnmcp

891
1
10
17

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 – Rajat Feb 03 '17 at 11:02
1

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. – Philip Stratford Jul 12 '18 at 08:53

score 4 · Answer 6 · answered May 20 '10 at 07:48

4

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

answered May 20 '10 at 07:48

Kyle Rosendo

25,001
7
80
118

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. – Mike Cheel Aug 03 '17 at 17:42
@MikeCheel doesn't work for me in ssms version 17.9.1 – reggaeguitar May 12 '20 at 22:23

score 3 · Answer 7 · answered Jul 26 '12 at 15:01

3

@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).

answered Jul 26 '12 at 15:01

Patrick

1,766
1
15
27

doesn't work for me - no column "AUTO_INC_INCREMENT" – Matthias Wuttke Mar 07 '16 at 07:04
You have an extra underscore – Patrick Mar 07 '16 at 15:58
I don’t see an `AUTOINC_INCREMENT` in SQL Server 11.0.7001. – binki Jan 01 '20 at 00:32
So this is specific to CE? Ah. – binki Jan 01 '20 at 00:33

score 3 · Answer 8 · edited Jul 23 '20 at 02:41

3

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

You get numeric value if table has identity

edited Jul 23 '20 at 02:41

Ashwin

52
8

answered Jan 12 '20 at 04:49

johnj1234

31
2

This worked perfectly for me, thank you (SQL Svr 2017) – Belladonna Mar 24 '22 at 23:24

score 3 · Answer 9 · answered May 22 '22 at 10:17

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.

score 1 · Answer 10 · edited Apr 20 '16 at 16:13

1

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

edited Apr 20 '16 at 16:13

Devart

119,203
23
166
186

answered Sep 05 '14 at 09:56

Aravind Goud

120
2
17

score 1 · Answer 11 · edited Jan 05 '20 at 06:15

1

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

--As simple as that!

edited Jan 05 '20 at 06:15

itwasntme

1,442
4
21
28

answered Jan 04 '20 at 21:49

user3415785

11
1

score 1 · Answer 12 · answered Oct 30 '20 at 09:49

1

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'

answered Oct 30 '20 at 09:49

Ashish Thombre

11
3

score 1 · Answer 13 · answered Mar 15 '22 at 14:11

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

score 0 · Answer 14 · answered Jul 05 '19 at 09:11

0

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'

answered Jul 05 '19 at 09:11

Sayed Muhammad Idrees

1,245
15
25

score 0 · Answer 15 · answered Jan 17 '23 at 09:20

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'

How to identify whether the table has identity column

15 Answers15

Linked