Posts Tagged ‘Finding Identity in SQL server’

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
    ‘U’