Find all SQL Identity columns

 
SELECT   '['+table_schema+'].['+table_name+'] .['+column_name+']'AS SchemaTable,
         table_name                                                            ,
         column_name                                                           ,
         ordinal_position                                                      ,
         data_type
FROM     information_schema.columns
WHERE    columnproperty(object_id(table_schema + '.' + table_name), column_name,'IsIdentity') = 1
ORDER BY table_name


SELECT   '['+table_schema+'].['+table_name+'] .['+column_name+']'AS SchemaTable,
         table_name                                                            ,
         column_name                                                           ,
         ordinal_position                                                      ,
         data_type                                                             ,
         Seed_Value                               AS SeedValue                                               ,
         Increment_Value                          AS IncrementValue                                          ,
         ident_current(Object_Name(ic.Object_ID)) AS CurrentValue                                            ,
         Last_Value                               AS LastValue
FROM     information_schema.columns               AS isc
         INNER JOIN sys.identity_columns          AS ic
         ON       ic.object_id = OBJECT_ID('['+isc.table_schema+'].['+isc.table_name+']')
         INNER JOIN sys.tables
         ON       SYS.tables.object_id                                                            = ic.object_id
WHERE    columnproperty(object_id(isc.table_schema + '.' + table_name), column_name,'IsIdentity') = 1 --( To get the table which contains only identity columns')
     AND SYS.tables.type                                                                          = 'u'
ORDER BY table_name