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'

Read more: Beyond Relational

Posted via email from .NET Info