How to get the list of all tables that have identity columns

Question

I would like to learn how to fetch list of all tables that have identity columns from a MS SQL database.

Giavani

Answer 1

SELECT 
  [schema] = s.name,
  [table] = t.name
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
  ON s.[schema_id] = t.[schema_id]
WHERE EXISTS 
(
  SELECT 1 FROM sys.identity_columns
    WHERE [object_id] = t.[object_id]
);

Milan

Answer 2

The select itself is very straightforward, using the ANSI system of INFORMATION_SCHEMA views. The obscure part is the COLUMNPROPERTY function. The function retrieve one property associated with a column. In this case the IsIdentity property, which marks if a column uses the identity property

      select COLUMN_NAME, TABLE_NAME
      from INFORMATION_SCHEMA.COLUMNS
       where TABLE_SCHEMA = 'dbo'
       and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
       order by TABLE_NAME

Corkboard

Please add a brief explanation of your code. Code-only answers are (sometimes) good but code+explanation answers are (always) better. – Lachrymal

Answer 3

I like this approach because it uses a join instead of a WHERE EXISTS or a call to COLUMNPROPERTY. Note that the group by is only necessary if you a) have tables with more than one IDENTITY column and b) don't want duplicate results:

SELECT 
    SchemaName = s.name,
    TableName = t.name
FROM
    sys.schemas AS s
    INNER JOIN sys.tables AS t ON s.schema_id = t.schema_id
    INNER JOIN sys.columns AS c ON t.object_id = c.object_id
    INNER JOIN sys.identity_columns AS ic on c.object_id = ic.object_id AND c.column_id = ic.column_id
GROUP BY
    s.name,
    t.name
ORDER BY
    s.name,
    t.name;

Whity

Answer 4

The script below will do:

SELECT a.name as TableName,
  CASE WHEN b.name IS NULL
    THEN 'No Identity Column'
    ELSE b.name
  END as IdentityColumnName
FROM sys.tables a
  LEFT JOIN sys.identity_columns b on a.object_id = b.object_id 

Selfexecuting

Answer 5

Old post, I know, but.. if you're in the same database as what you're trying examine the tables in (like all the other scripts on this thread do), you can avoid all explicit joins by turning to some very useful functions. Comment out the DataType line and the ,* line if you don't need them.

 SELECT  SchemaName = OBJECT_SCHEMA_NAME(object_id)
        ,ObjectName = OBJECT_NAME(object_id)
        ,DataType   = TYPE_NAME(system_type_id)
        ,*
   FROM sys.identity_columns
;

Argive

Answer 6

Select OBJECT_NAME(object_Id) Rrom sys.identity_columns where is_identity = 1;

Halt