How to find list of all FOREIGN KEY, UNIQUE and PRIMARY KEY CONSTRAINTS


How to find list of all FOREIGN KEY, UNIQUE and PRIMARY KEY CONSTRAINTS

Method-1

You can always manually check the Constraint details using SQL Server Management Studio. However, if you just want to check all the Constraints and related tables in just one report, then this article will be a great help.

To know more about Constraints in SQL server, refer to below TechNet Article

http://technet.microsoft.com/en-us/library/ms189862(v=sql.105).aspx

Method-2

Sometimes to troubleshoot issues like Duplicate Keys, Constraints error and etc., we need details of the Keys and their corresponding table. Using INFORMATION_SCHEMA.TABLE_CONSTRAINTS you can view all the contraints for corresponding tables in a database. The bleow query give you result with Constraint_name, table_name and constraint_type columns and display all the Key Constraints in database.

SELECT constraint_name, table_name,constraint_type

FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS

If you want to display only a specific type of Key constraints then you can use the below script. In this example we will get result of all UNIQUE Key Constraints. You can modify the highlight part in script to get other constraints list.

SELECT constraint_name, table_name,constraint_type

FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS

WHERE constraint_type =
UNIQUE

ORDER
BY table_name


Method-3

If you want to see the Constraint details for a particular table, then you simply execute

SP_HELP <table name>

Let’s say I want to see all the associated constraints for table AccountBase then our script will be

sp_help AccountBase


For more article updates, videos and posters join our official page in Facebook

Facebook Page: https://www.facebook.com/ServerGeeks

Web Site: https://servergeeks.wordpress.com/

Video Channel: https://www.youtube.com/user/Habibmvp

Advertisements

One thought on “How to find list of all FOREIGN KEY, UNIQUE and PRIMARY KEY CONSTRAINTS

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