.NET Information center: SQL Server - Identifying identity columns using TSQL

Thursday, August 19, 2010

SQL Server - Identifying identity columns using TSQL

One of my friends asked me if it is possible to identify the tables with identiy columns as he wanted to run dbcc checkident command to all the tables of the database.There are actually many ways to identify identity columns from a table.The following three methods will list out the table names and the identity column name (if available)
Method 1 : Use columnproperty function

select         
        table_name,column_name 
from    
        information_schema.columns 
where        
        columnproperty(object_id(table_name)
        ,column_name,'isidentity')=1 
order by 
        table_name

Method 2 : Use sys.all_columns view

select   
         object_name(ac.object_id)
         ,so.name 
from         
         sys.all_columns as ac 
         inner join sys.objects as so on object_name(ac.object_id)=so.name 
where         
         is_identity=1 and so.type='u'