Question I received in Email : How to list all the columns in the database which are used as identity key in my database?
Answer: Run following query in query editor.
USE AdventureWorks
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name,
t.name AS table_name,
c.name AS column_name
FROM sys.tables AS t
JOIN sys.identity_columns c ON t.OBJECT_ID = c.OBJECT_ID
ORDER BY schema_name, table_name;
GO
Reference : Pinal Dave (http://blog.SQLAuthority.com)
This will provide you with the columns that have Identity insert on, but not the Key columns
Another method that works in all versions is
select table_schema, table_name,column_name from information_schema.columns
where columnproperty(object_id(table_name),column_name,’IsIdentity’)=1
order by table_schema, table_name
[...] List All Column With Indentity Key In Specific Database A to the point blog post where I write a script which provides the answer right away to the question in the title. [...]