WikiDBA

Sometimes we have to work on identity columns and we were not aware about tables and columns which belongs to Identity property. Here are some options to get the tables having identity columns.

1. SELECT OBJECT_NAME(Object_ID),Name from sys.identity_columns
   
2. SELECT OBJECT_NAME(ID) as ‘Table Name’, Name as ‘Column Name’ FROM syscolumns WHERE status = 0x80 
   
3. SELECT OBJECT_NAME(Object_ID), Name from sys.columns where is_identity=1
   
4. SELECT OBJECT_NAME(id) as TableName, name as ColumnName FROM syscolumns
   WHERE COLUMNPROPERTY(id, name,‘IsIdentity’)= 1
   
5. SELECT OBJECT_NAME(object_id) as TableName, name as ColumnName FROM Sys.Columns
   WHERE COLUMNPROPERTY(object_id, name, ‘IsIdentity’) = 1
   
6. SELECT OBJECT_NAME(Object_ID),Name from sys.identity_columns
   
7. SELECT Name,OBJECTPROPERTY(id, ‘TableHasIdentity’) AS TableHasIdentity from sysobjects
   WHERE xtype = ‘U’
   
8. SELECT Name, OBJECTPROPERTY(object_id, ‘TableHasIdentity’) AS TableHasIdentity from sys.objects
   WHERE type = ‘U’