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(IDas ‘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(idas TableName, name as ColumnName FROM syscolumns
    WHERE COLUMNPROPERTY(id, name,‘IsIdentity’)= 1
  5. SELECT OBJECT_NAME(object_idas TableName, name as ColumnName FROM Sys.Columns
    WHERE COLUMNPROPERTY(object_idname‘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 NameOBJECTPROPERTY(object_id‘TableHasIdentity’AS TableHasIdentity from sys.objects
    WHERE type

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s