Tables under AdventureWorks database

• ram4tech

  Hall of Fame

  Points: 3140

  More actions

  June 27, 2007 at 1:28 am

  #179239

  Can someone point out what's wrong with the following query. I want to see a list of tables that have columns with identity property.

  SELECT c.*,COLUMNPROPERTY(OBJECT_ID(c.TABLE_NAME), c.COLUMN_NAME, 'IsIdentity'), t.*

  FROM INFORMATION_SCHEMA.TABLES t

  INNER JOIN INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME

  ORDER BY t.TABLE_NAME

  Thank you.

  -R

• Sugesh Kumar

  One Orange Chip

  Points: 27311

  More actions

  June 27, 2007 at 1:47 am

  #715446

  The given query below will give what you wanted the table name and column name for all columns that have identity property set in the database.

  select

  a.name as ColumnName,b.name as tableName from sys.columns a, sys.tables b where a.object_id = b.object_id and a.is_identity = 1

  Cheers,
  Sugeshkumar Rajendran
  SQL Server MVP
  http://sugeshkr.blogspot.com