How to get a list of all identity columns in a database

Here is some code to get a list of identity columns in database.

select as TableName,  + o.list as IdentityColumnName 
from    sysobjects so
cross apply
           case when exists ( 
        select id from syscolumns
        where object_name(id)
        and name=column_name
        and columnproperty(id,name,'IsIdentity') = 1 
        ) then
        end + ' '
     from information_schema.columns where table_name =
    ) o (list)
where   xtype = 'U'
AND o.list is not null
AND name    NOT IN ('dtproperties')

posted @ Monday, November 11, 2013 4:14 PM


Comments on this entry:

No comments posted yet.

Your comment:

 (will not be displayed)

Please add 2 and 8 and type the answer here:

Live Comment Preview: