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

Friday, May 1, 2009 |

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

0 comments: