Find Primary key, foreign key in all columns and all table: SQL Server 2005

We generally used to find primary key or foreign key by SSMS or by using SP_help for one table. Sometime we need to list out all primary key and/or foreign key and/or CHECK constraint and/or UNIQUE key available in database for all table and columns. How can we do that? I had generated very small and handy script for my personal use which I am going to share with you now.

--not only primary key, you can find

--CHECK

--FOREIGN KEY

--PRIMARY KEY

--UNIQUE KEY

select p.table_name,c.column_name,p.constraint_name

from

information_schema.table_constraints p join

information_schema.key_column_usage c

ON

p.table_name=c.table_name

and

p.constraint_name=c.constraint_name

where p.constraint_type='Primary Key'

order by p.table_name,c.column_name

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com