Showing posts with label Identity. Show all posts
Showing posts with label Identity. Show all posts

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