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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: