Microsoft SQL Server programming FAQ
Last updated: February 9th '02 | Best viewed with: All popular browsers | Best viewed at: 1024x768 | Links to external sites will open in a new window
About myself
My technical skills
My favorites
My picture album

Shortcut keys
My code library

VB resources
SQLServer resources
SQLServer books
Replication FAQ
Scripting resources
ASP resources

Search my site
Sign my guestbook
Contact information

SQL Server Articles New

 

You are here: Home > Microsoft SQL Server FAQ > Programming FAQ

NEW!!! Subscribe to my newsletter:
Want to keep in touch with the latest in SQL Server world? Email vyaskn@hotmail.com with 'subscribe' in the subject line

How to find out all the IDENTITY columns of all the tables in a given database? <back>

The following query will output a list of all the tables that have IDENTITY columns, along with the column name and data type:

SELECT '[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' AS [Table Name], COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMNPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), COLUMN_NAME, 'IsIdentity') = 1
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
--AND TABLE_NAME = 'Employees' /* If you are interested in a specific table*/