How do you determine what SQL Tables have an ident

Question

I want to create a list of columns in SQL Server 2005 that have identity columns and their corresponding table in T-SQL.

Results would be something like:

TableName, ColumnName

Answer 1

By some reason sql server save some identity columns in different tables, the code that work for me, is the following:

select      TABLE_NAME tabla,COLUMN_NAME columna
from        INFORMATION_SCHEMA.COLUMNS
where       COLUMNPROPERTY(object_id(TABLE_SCHEMA+'.'+TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
union all
select      o.name tabla, c.name columna
from        sys.objects o 
inner join  sys.columns c on o.object_id = c.object_id
where       c.is_identity = 1

Answer 2

Another potential way to do this for SQL Server, which has less reliance on the system tables (which are subject to change, version to version) is to use the INFORMATION_SCHEMA views:

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

Answer 3

This query seems to do the trick:

SELECT 
    sys.objects.name AS table_name, 
    sys.columns.name AS column_name
FROM sys.columns JOIN sys.objects 
    ON sys.columns.object_id=sys.objects.object_id
WHERE 
    sys.columns.is_identity=1
    AND
    sys.objects.type in (N'U')

Answer 4

I think this works for SQL 2000:

SELECT 
    CASE WHEN C.autoval IS NOT NULL THEN
        'Identity'
    ELSE
        'Not Identity'
    AND
FROM
    sysobjects O
INNER JOIN
    syscolumns C
ON
    O.id = C.id
WHERE
    O.NAME = @TableName
AND
    C.NAME = @ColumnName

Answer 5

sys.columns.is_identity = 1

e.g.,

select o.name, c.name
from sys.objects o inner join sys.columns c on o.object_id = c.object_id
where c.is_identity = 1

Answer 6

The following query work for me:

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