Following script are very useful to know all the constraint in the database. I use this many times to check the foreign key and primary key constraint in database. This is simple but useful script from my personal archive.
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 (https://blog.sqlauthority.com)
SQL SERVER – 2005 – List All The Constraint of Database – Find Primary Key and Foreign Key Constraint in Database
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
101 Comments. Leave new
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
Its really helpful infromation.
Lots of hidden trissures are found in all articales of this web
RELALLY GRT ONE
Itz good…..
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.
This query works and was very helpful to me. Thanks.
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
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!!!!!
sir,
i got my answer, initially i didn’t search in sqlauthority.com!!
thx
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?
1 column should be for continent.
Put ‘Asia’ in as the value for all the Asian coutries, etc.
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
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
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
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!!!!
Thanks for the script above, it really helped me on deleting my DWH constraints. Cheers.
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!
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.
szolarp & Tushar
Many thanks .. you really helped me ..
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!!!!!
What r the differences b/w Sql Server 2005 and 2000
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
it is so useful…… :)
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