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'

You can also simply use

select 
	object_name(object_id),name 
from 
	sys.all_columns 
where 
	is_identity=1  and objectproperty(object_id,'isusertable')=1

Method 3 : Use sys.identity_columns view

select 
	object_name(id.object_id),so.name 
from 
	sys.identity_columns as id inner join sys.objects as so
	on object_name(id.object_id)=so.name
where 
	so.type='u'

and simplified code is

select 
	object_name(object_id),name 
from 
	sys.identity_columns 
where 
	objectproperty(object_id,'isusertable')=1