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
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
101 Comments. Leave new
I have used the following query. But this gives me the duplicates.I want to get unique result.
select o1.name as [Referencingtable] ,c1.name as [Referencingcolumn], o2.name as [Referencedtable] ,c2.name as [Referencedcolumn]
from sysobjects o1 join syscolumns c1 on o1.id = c1.id
join (sysobjects o2 join syscolumns c2 on o2.id = c2.id) on c1.[name] = c2.[name]
where o1.xtype = ‘u’ and o2.xtype = ‘u’ and o1.name != o2.name
Really Cool site man……98% of my SQL server doubts are resolved in your site….
Pinal U Rule
Hello Sir,
The query to get all Database level constraints is :
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’
which u have already given……….
But my issue is
I am having 100 databases so for that i have to run the above query in all databases one by one…….
i tried sp_msforeachdb but not working…….
You have any query which will give me the above details for all Datbases in one shot…
Try this code
sp_msforeachdb ‘
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”’
If you want to include database name too, use this
sp_msforeachdb ‘
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”’
if msforeachdb not working then try with cursor…
just giving you an example, which will go in each database and will print the database name using cursor. you can modify it as per your requirement :-
declare @name varchar(200)
declare c1 cursor for
select name from master..sysdatabases
open c1
FETCH NEXT FROM c1 into @name
WHILE @@FETCH_STATUS = 0
begin
print @name + ‘ is database name’
FETCH NEXT FROM c1 into @name
end
close c1
deallocate c1
Hi Pinal,
Generally I get any new comments addedd on this website to my mail id. But since last 2 days I am no more getting it.
Is there anything changed on this blog?
I think it is better when we change the condition
SELECT *, OBJECT_NAME(OBJECT_ID) AS ConstraintName,SCHEMA_NAME(schema_id) AS SchemaName,OBJECT_NAME(parent_object_id) AS TableName,type_desc AS ConstraintType FROM sys.objects WHERE (OBJECTPROPERTY(object_Id, N’IsConstraint’) = 1)
i don’t know if we gain a performance boost or not but it looks more reliable to me
@abhay
Really helpfull….Thanks
While I was working in SQL server 2005, I used to know domain constraint have higher priority than referential constraint. Now I am little bit confusion regarding the priority among constraints in sql server. I will be thankful if u give a brief discussion regarding priority among constraints.
Does any one have an idea about how to get the violated constrained name.
eg If I get an error Violation of PRIMARY KEY constraint ‘pk24’. Cannot insert duplicate key in object ‘xyz’.
I would like to catch pk24 alone.
Is there a way out?
exec sp_pkeys xyz
madhivanan,
ERROR_NUMBER() – catches error no,
ERROR_MESSAGE() – catches error msg,
ERROR_PROCEDURE() – catches procedure name ,
ERROR_LINE() – catches line no
How do i catch the constraint name if the error is about the violation of primary key?
You need to parse the error_message() and get that between contraint and cannot
hi sir,
explain about constraints and uses of constraints
Read about it in SQL Server help file
Sir,
The query of Default Constraint which u have given,i executed that but at d time of inserting d records in d table d is error has occured.
The default value which is hav to go automatically in the table that has not done.
Plz send me d feedback that how to do that.
Thanks.
hello sir i need ur help …
would u ple send me sql information
if object_id(‘tempdb..#tmp’) is not null
drop table #tmp
select
‘[‘ + SCHEMA_NAME(o1.schema_id) + ‘].[‘ + o1.name + ‘]’ as Referencing_Object_name
, s.name as Constraint_name
, c1.name as referencing_column_Name
, ‘[‘ + SCHEMA_NAME(o2.schema_id) + ‘].[‘ + o2.name + ‘]’ as Referenced_Object_name
, c2.name as Referenced_Column_Name
, fk.keyno as orderKey
, ‘[‘ + SCHEMA_NAME(o1.schema_id) + ‘].[‘ + s.name + ‘]’ Constraint_name_schema
into #tmp
from sysforeignkeys fk
inner join sys.objects o1 on fk.fkeyid = o1.object_id
inner join sys.objects o2 on fk.rkeyid = o2.object_id
inner join sys.columns c1 on c1.object_id = o1.object_id and c1.column_id = fk.fkey
inner join sys.columns c2 on c2.object_id = o2.object_id and c2.column_id = fk.rkey
inner join sys.objects s on fk.constid = s.object_id
and o2.name='<>’ — this predicate for a specific table
order by 1, fk.keyno
select distinct
‘IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N”’+ Constraint_name_schema + ”’) AND parent_object_id = OBJECT_ID(N”’ + Referencing_Object_name + ”’))’ + char(13) + char(10) + char(9) +
‘ALTER TABLE ‘ +
Referencing_Object_name +
‘ DROP CONSTRAINT ‘ +
Constraint_name
from #tmp o1
select distinct
‘ALTER TABLE ‘ +
Referencing_Object_name +
‘ WITH NOCHECK ADD CONSTRAINT ‘ +
Constraint_name +
‘ FOREIGN KEY ‘ +
‘(‘ +
STUFF(( SELECT
‘],[‘ + c1.referencing_column_Name
FROM #tmp c1
where c1.Referencing_Object_name = o1.Referencing_Object_name
group by c1.referencing_column_Name,orderKey
order by orderKey
FOR XML PATH(”)
), 1, 2, ”) + ‘]’
+ ‘)’ + char(13) + char(10) + char(9) +
‘ REFERENCES ‘ +
Referenced_Object_name +
‘(‘ +
STUFF(( SELECT
‘],[‘ + c1.Referenced_Column_Name
FROM #tmp c1
where c1.Referenced_Object_name = o1.Referenced_Object_name
group by c1.Referenced_Column_Name,orderKey
order by orderKey
FOR XML PATH(”)
), 1, 2, ”) + ‘]’
+ ‘)’
from #tmp o1
does anybody know how to list the column names which are included in the check constraint
Look at these views
select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
select * from INFORMATION_SCHEMA.CHECK_CONSTRAINTS
Hello,
Is there any way to get the data in one table is referencing in any other tables
For eg: I have 3 tables say tbl1,tbl2,tbl3.
tbl1 contains 3 records say 1,2,3
But only 1 and 2 are referencing tbl2 and tbl3,
If we delete the record 1 or 2 from tbl1 ,it is not possible.since the data is using in tbl2 or tbl3.
But if delete 3 from tbl1 it is possible.
So my question is, the way to find out whether the data is referencing in some other places without writing the delete query.
Through proc I pass the values 1,2,3 etc.
Thanks.
Manoj
Use join or exists
select * from table as t exists(select * from othertable ot where t.id=ot.id)
I think it will not solve my problem.
The following query returns all related tables for tbl1.
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=’tbl1′ and c2.name=’ID’
I also need to get is there any data in the referenced tables.
We can do this by writing a dynamic query by checking count in each table.
Other than this i am looking for a simplest way.
When we delete a record from the main table, and it has some references,we usually get the below error msg.
The DELETE statement conflicted with the REFERENCE constraint “FK_tbl3_tbl1″. The conflict occurred in database ” db1 “, table “dbo.tbl3”, column ‘ID’.
The statement has been terminated.
So definitely SQL Server should have some system procedures, that is checking this dependencies(means existence of records in related tables).
What is that sp..?
Thanks
how to find out not null constraints columns in a table using sql command
Anyone know if there is any way to get the constaintas tha are defined as “with check add”?
Sir, if i ve given name to some constraints such as pk1 etc and at last if i want to know the constraint names that i ve assigned then hw can i see using select statements? Plz reply
A common scenario is where one needs write a schema change script to drop a column from a table, but first a constraint that references the column must be dropped. If the constraint has a name that was auto generated (ex: DF__MyTable_MyColumn__26E730D3), then that’s problematic, because the name may be different across dev, qa, prod, etc. So, here is an example for dynamically determining the name of constraint and then dropping it.
declare @myconstraint varchar(8000);
select @myconstraint = name
from sys.default_constraints
where object_name(parent_object_id) = ‘mytable’
and col_name (parent_object_id, parent_column_id) = ‘mycolumn’;
if @myconstraint is not null
exec(‘alter table mytable drop constraint ‘+@myconstraint);