Monday, January 12, 2009

How do you find out all the IDENTITY columns of all the tables in a given database?

How do you find out all the IDENTITY columns of all the tables in a 
given database?

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


No comments: