SQL SERVER - 2005 - List All The Constraint of Database - Find Primary Key and Foreign Key Constraint in Database

http://blog.sqlauthority.com/2007/09/16/sql-server-2005-list-all-the-constraint-of-database-find-primary-key-and-foreign-key-constraint-in-database/

USE AdventureWorks;
GO
SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'
GO

Reference : Pinal Dave (http://www.SQLAuthority.com)
Ads by Google
免費 SQL Server 2008 軟件
Get Free Software by Attending SQL Server 2008 Official Training
www.welkin.com.hk/promotions
Mssql Monitoring
Monitor SQL Server Performance Track DB usage. Download Now!
manageengine.adventnet.com
MS SQL Repair SW
Repair Corrupt MDF Database Files Instant Repair, Free Demo Preview
www.mssqldatabaserecovery.com
Sql Server
Ready to learn SQL Server? Try our free videos before you buy!
cbtnuggets.com


Posted in Author Pinal, Database, SQL, SQL Authority, SQL Constraint and Keys, SQL Documentation, SQL Download, SQL Error Messages, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Tips and Tricks, T SQL, Technology | 25 Comments
25 Responses to “SQL SERVER - 2005 - List All The Constraint of Database - Find Primary Key and Foreign Key Constraint in Database”

1.
on September 16, 2007 at 11:07 pm abhay

Dear Pinal,

To get the idea of any relationship between tables using foreign key relationships, I think, following query will be more useful :

select Referencing_Object_name, referencing_column_Name, Referenced_Object_name, Referenced_Column_Name from
(select Referenced_Column_Name = c.name, Referenced_Object_name = o.name, f.constid from sysforeignkeys f, sysobjects o, syscolumns c
where (f.rkeyid = o.id) and c.id = o.id and c.colid = f.rkey) r,
(select referencing_column_Name = c.name, Referencing_Object_name = o.name, f.constid from sysforeignkeys f, sysobjects o, syscolumns c
where (f.fkeyid = o.id) and c.id = o.id and c.colid = f.fkey) f
where r.Referenced_Column_Name = f.referencing_column_Name
and r.constid = f.constid
order by f.Referencing_Object_name

Regards,
Abhay

2.
on September 30, 2007 at 10:38 pm Ujjaval Suthar

Hey Pinal,

I was going through google to look for the query that you’ve posted here and came across your blog.

Its surprising to see you and remember your face from Nirma Bus, if my memory is not wrong. I was studying in Nirma between 1999 - 2003. And you are also from Gandhingar, right? yeah I am from Gandhinagar.

Regards,
Ujjaval

PS:- Oh by the way, useful post. Thanks for that.

3.
on October 2, 2007 at 6:03 pm pinaldave

Hi Ujjaval Suthar,

Yes, I am Pinal from Gandhinagar. Nirma 1999-2003.

Regards,
Pinal

4.
on October 23, 2007 at 7:43 am Rob

This query works and was very helpful to me. Thanks.

5.
on December 21, 2007 at 1:11 am Shishir Khandekar

For SQL Server 2005, you can use the sys.foreign_keys view to achieve the same. The columns in this view are self explanatory so am not publishing the query here.

Regards
Shishir

6.
on December 21, 2007 at 5:47 pm Sandeep

i’m new to sql server 2005 …
can u plz suggest ny gud book for sqlserver 2005 for a beginner and i want to do certification for d same..

plz guide!!!!!

7.
on December 21, 2007 at 6:11 pm Sandeep

sir,
i got my answer, initially i didn’t search in sqlauthority.com!!

thx

8.
on December 28, 2007 at 2:25 pm rajesh

Hello Sir,
I am a .net Developer and I came across some difficult questions during the interview on database. The question
is :
There is a table with 5 coloumns, in that under country coloumn the fields should be all asain countries. If the user
enters other than asian country it should throw an error.
How to write a query for this?

9.
on January 18, 2008 at 7:21 pm Tom W

1 column should be for continent.
Put ‘Asia’ in as the value for all the Asian coutries, etc.

10.
on February 22, 2008 at 3:20 pm szolarp

Hi!
I made an easier to understand script that shows all constraint in the database including that rows where the referencing_column_name and the referenced_column_name are different and the name of the constraint
select
o1.name as Referencing_Object_name
, c1.name as referencing_column_Name
, o2.name as Referenced_Object_name
, c2.name as Referenced_Column_Name
, s.name as Constraint_name
from sysforeignkeys fk
inner join sysobjects o1 on fk.fkeyid = o1.id
inner join sysobjects o2 on fk.rkeyid = o2.id
inner join syscolumns c1 on c1.id = o1.id and c1.colid = fk.fkey
inner join syscolumns c2 on c2.id = o2.id and c2.colid = fk.rkey
inner join sysobjects s on fk.constid = s.id
inner join syscolumns c1 on c1.id = o1.id and c1.colid = fk.fkey
inner join syscolumns c2 on c2.id = o2.id and c2.colid = fk.rkey
inner join sysobjects s on fk.constid = s.id

11.
on March 27, 2008 at 10:44 pm Tushar Mehere

Hi szolarp,

Your query is prefect. It had some defect, i refined it below.

select
o1.name as Referencing_Object_name
, c1.name as referencing_column_Name
, o2.name as Referenced_Object_name
, c2.name as Referenced_Column_Name
, s.name as Constraint_name
from sysforeignkeys fk
inner join sysobjects o1 on fk.fkeyid = o1.id
inner join sysobjects o2 on fk.rkeyid = o2.id
inner join syscolumns c1 on c1.id = o1.id and c1.colid = fk.fkey
inner join syscolumns c2 on c2.id = o2.id and c2.colid = fk.rkey
inner join sysobjects s on fk.constid = s.id
and o2.name=’tblUserDetails’ — this predicate for a specific table

12.
on March 29, 2008 at 7:42 am Aviv

Hi
Does anyone know how can I find the relationship between colomn and default constraint. I need it for dynamicly drop the column in different databases

13.
on April 16, 2008 at 4:34 pm Rakesh Dewangan

Hi Pinal,
really this procedure its very very useful,
I was facing trouble from last half an hour, and your query has resolved my problem, with in second.

thanks a lot.

great work!!!!

14.
on May 5, 2008 at 2:57 pm Henko

Thanks for the script above, it really helped me on deleting my DWH constraints. Cheers.

15.
on May 12, 2008 at 5:07 pm Gerry

This blog helped me a lot! Many thanks to you all.

I was looking for days to solve my constraint problem.
this is very usefull in my ADO application. Looks like ADO does not have functions to read detailed constraint information like this script from an SQL database. Therefore I was forced to search for a solution in SQL scripting and here it was!

Thx!

16.
on May 24, 2008 at 6:16 pm Niraimathi

Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the REFERENCE constraint “hrpyprc_ps_auth_r01_fk”. The conflict occurred in database “HRMS40_CBI”, table “dbo.hrpyprc_payset_auth_cnt”.
The statement has been terminated.

17.
on June 23, 2008 at 2:41 pm Tim4it

szolarp & Tushar

Many thanks .. you really helped me ..

18.
on August 4, 2008 at 5:00 pm kalyan

i’m new to sql server 2005 …
can u plz suggest ny gud book for sqlserver 2005 for a beginner and i want to do certification for d same..

plz guide!!!!!

19.
on August 7, 2008 at 4:47 pm Ankush

What r the differences b/w Sql Server 2005 and 2000

20.
on August 26, 2008 at 10:04 am Muhammad Usman Arshad

Hi,

I think using information schema is much useful than using sysobjects. To see the list of constraints you can use the query:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

or you can use database name with it:

SELECT * FROM [SugarCMS].INFORMATION_SCHEMA.TABLE_CONSTRAINTS

Here is the complete list of information_schema views:

CHECK_CONSTRAINTS: Check Constraints
COLUMN_DOMAIN_USAGE: Every column that has a user-defined data type.
COLUMN_PRIVILEGES: Every column with a privilege granted to or by the current user in the current database.
COLUMNS:Lists every column in the system
CONSTRAINT_COLUMN_USAGE: Every column that has a constraint defined on it.
CONSTRAINT_TABLE_USAGE: Every table that has a constraint defined on it.
DOMAIN_CONSTRAINTS: Every user-defined data type with a rule bound to it.
DOMAINS: Every user-defined data type.
KEY_COLUMN_USAGE: Every column that is constrained as a key
PARAMETERS: Every parameter for every user-defined function or stored procedure in the datbase. For functions this returns one row with return value information.
REFERENTIAL_CONSTRAINTS: Every foreign constraint in the system.
ROUTINE_COLUMNS: Every column returned by table-valued functions.
ROUTINES: Every stored procedure and function in the database.
SCHEMATA: Every database in the system.
TABLE_CONSTRAINTS: Every table constraint.
TABLE_PRIVILEGES: Every table privilege granted to or by the current user.
TABLES: Every table in the system.
VIEW_COLUMN_USAGE: Every column used in a view definition.
VIEW_TABLE_USAGE: Every table used in a view definition.
VIEWS: Every View

21.
on August 29, 2008 at 10:27 pm Syed

Hi,
Does any one know how to get DEFAULT Constraint along with the name of the column on which it has defined.
Nirmal query gives the table name, but not the column name, because most of the time DEFAULT is created on the column by using DEFAULT, which create the system generated name of the DEFAULT, so I would like to know how to get the column name along with the table name.

I would appreciate any suggestion.

Thanks

22.
on August 30, 2008 at 3:44 am Imran Mohammed

@SYED,

This might help,

SELECT OBJECT_NAME(PARENT_OBJECT_ID) TABLE_NAME,
COL_NAME (PARENT_OBJECT_ID, PARENT_COLUMN_ID)COLUMN_NAME ,
NAME DEFAULT_CONSTRAINT_NAME
FROM SYS.DEFAULT_CONSTRAINTS ORDER BY 1

Hope this helps,
Imran.

23.
on September 12, 2008 at 10:51 pm Arthur

I think is easier… it’s pretty much like the one publish by szolarp and “fixed” by Tushar Mehere…

is this:
SELECT RO.NAME AS ParentTable, RC.NAME AS ParentColumn, FO.NAME AS ForeignTable, FC.NAME AS ForeignColumn
FROM sysforeignkeys F INNER JOIN sysobjects RO
ON F.rkeyid = RO.id INNER JOIN syscolumns RC
ON RC.id = RO.id AND RC.colid = F.rkey INNER JOIN sysobjects FO
ON F.fkeyid = FO.id INNER JOIN syscolumns FC
ON FC.id = FO.id AND FC.colid = F.fkey
ORDER BY RO.NAME, RC.NAME, FO.NAME, FC.NAME

we don’t really to know the name…

24.
on September 27, 2008 at 4:17 am Mark

Hi Pinal,
I have a situation wherein I need to change a particular name in my entire database.
Please tell me how can i trace this particular word - it might be occuring in ‘n’ number of tables in the database, in ‘n’ number of column values and in ‘n’ number of records throughout present in any table of that database.
I need to write a cursor, go to each record and trace that word, likewise for others, for each table. I heard that sysobjects and syscolumns could help in this scenario, could you please generate the code if possible.

Thanks, Mark