List all the columns in the database which are used as identity key

-- List all columns which are used as identity key
-- sample1
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;

-- On/Off to disable identity key constrain

SET IDENTITY_INSERT [MyTableName] ON;
....
SET IDENTITY_INSERT [MyTableName] OFF;


source from: http://blog.sqlauthority.com/2008/03/29/sql-server-2005-list-all-column-with-indentity-key-in-specific-database/