How do I find IDENTITY columns on Table using T-SQL

How do I find IDENTITY columns on Table using T-SQL

• JulyBetaDoesntWork

  Found it, a little obscure:

  SELECT obj.[name], col.[name], col.[colstat], col.*
  FROM [syscolumns] col
  JOIN [sysobjects] obj
  ON obj.[id] = col.[id]
  WHERE obj.type = 'U'
  AND col.[status] = 0x80
  ORDER BY obj.[name]

  Does anyone know a way of doing this using an INFORMATIO_SCHEMA view

  
  

• t-m00re

  Here is some more ( in technicolor ;-) )

  USE northwind
  GO
  
  DECLARE @tableName VARCHAR(50)
  SELECT @tableName = 'orders'
  
  --Use COLUMNPROPERTY and the syscolumns system table
  SELECT COUNT(name) AS HasIdentity
  FROM syscolumns
  WHERE OBJECT_NAME(id) = @tableName
  AND COLUMNPROPERTY(id, name, 'IsIdentity') = 1
  GO
  
  
  DECLARE @intObjectID INT
  SELECT @intObjectID =OBJECT_ID('orders')
  
  --Use OBJECTPROPERTY and the TableHasIdentity property name
  SELECT COALESCE(OBJECTPROPERTY(@intObjectID, 'TableHasIdentity'),0) AS HasIdentity

  Denis the SQL Menace

  http://sqlservercode.blogspot.com/

  
  

• Magaviga

  I posted that sometime ago:
  
  SELECT IsIdentity=COLUMNPROPERTY(id, name, 'IsIdentity')
  FROM syscolumns WHERE OBJECT_NAME(id) = sometable_test'

  Mit Information_schema views from
  http://weblogs.asp.net/psteele/archive/2003/12/03/41051.aspx

  
  select TABLE_NAME + '.' + COLUMN_NAME, TABLE_NAME
  from INFORMATION_SCHEMA.COLUMNS
  where TABLE_SCHEMA = 'dbo'
  and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') =
  1
  order by TABLE_NAME

  
  HTH, Jens Suessmeyer.
  
  
  ---
  http://www.sqlserver2005.,de
  ---

  
  

• NeilDholakia

  Added to above answers, you can check the link too.

  http://www.sqlmag.com/Articles/ArticleID/16091/16091.html Ad=1

  Thanks

  Naras.

  
  
  

• M.CeYLaN

  select TABLE_NAME,COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
  where COLUMN_NAME like '%ID%'

  Here ID is the keyword we are looking for in the name of the column.

  
  

• mayankjohri

  You can also try this stored procedure will return all the properties of the table

  sp_column <TABLENAME>

  sp_MShelpcolumns <TABLENAME>

  
  
  

• Proxymo

  you can try the below sql script for finding the columns of table without using the views.

  Select syscolumns.name AS Columns,systypes.name as DataType,sysobjects.name As Tables

  from sysobjects,syscolumns,systypes

  Where sysobjects.id = syscolumns.id AND

  syscolumns.xtype = systypes.xtype AND

  syscolumns.xusertype = systypes.xusertype AND

  sysobjects.type = 'U' AND

  syscolumns.isnullable = 0 AND

  sysobjects.name = 'tablename'

  
  
• How do I find IDENTITY columns on Table using T-SQL