Get Primary, Foreign, Unique, Identity key column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Get Primary, Foreign, Unique, Identity key column

Author

Topic

baburk
Posting Yak Master

108 Posts

Posted - 2010-08-04 : 02:02:51

Hi all,

I wants to get the PrimaryKey, ForeignKey, UniqueKey, IdentityKey column names. I tried with the below query. But it returns all the column name.

DECLARE @TableName nVarchar(256)
SET @TableName = 'PkFkIkTest'

SELECT DISTINCT col.COLUMN_NAME, col.DATA_TYPE, col.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tblCons
INNER JOIN INFORMATION_SCHEMA.COLUMNS AS col ON col.TABLE_NAME = tblCons.TABLE_NAME AND tblCons.TABLE_NAME = @TableName
WHERE
tblCons.CONSTRAINT_TYPE = 'PRIMARY KEY'
OR tblCons.CONSTRAINT_TYPE = 'FOREIGN KEY'
OR tblCons.CONSTRAINT_TYPE = 'UNIQUE'

OR COLUMNPROPERTY (OBJECT_ID(col.Table_Name), col.Column_Name,'IsIdentity') = 1

Thanks,
Babu Kumarasamy

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-24 : 06:01:15

DECLARE @TableName nVarchar(256)
SET @TableName = 'PkFkIkTest'

select t1.table_name,t3.column_name,t2.constraint_name,t2.constraint_type from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE as t1
inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS as t2 on t1.table_name=t2.table_name
and t1.constraint_name=t2.constraint_name
inner join INFORMATION_SCHEMA.COLUMNS as t3 on t3.table_name=t1.table_name
and t1.column_name=t3.column_name
where t3.table_name=@TableName

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources