Monday, April 23, 2007

Primary Key

I was trying out something for my hobbylist project and i needed to write a query that would find me the primary key of a given table. The query also needs to check whether the primary key is identity.
Following is the solution I finally made out. It uses the System Information Schema views and columnproperty function to get the required results.

ColumnProperty ( object_id('dbo.Table1'),
(SELECT Column_Name FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cuJOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tcOn cu.Constraint_Name = tc.Constraint_Name Where cu.Table_Name = 'Table1'and tc.Constraint_type = 'PRIMARY KEY'),
'IsIdentity') as IfIdentity