Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how to list all constarints in a database

Author  Topic 

divyaram
Posting Yak Master

180 Posts

Posted - 2010-02-23 : 23:53:04

Hi all
How to get the all constraints in a database... i got the code for selecting foreign
key constraints of a database , i want to list all constraints in a database

the code which we will get the foreignkey constraints is
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
order by Referencing_Object_name


Regards,
Divya

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2010-02-24 : 00:18:48
Check out the sp_helpconstraint system proc.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

divyaram
Posting Yak Master

180 Posts

Posted - 2010-02-24 : 00:24:21
quote:
Originally posted by dinakar

Check out the sp_helpconstraint system proc.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/


Thank you Dinakar, buti want to list all default constarint at the same time .sp_help constraint we have give each table name.. i want list for whole database at one time

Regards,
Divya
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2010-02-24 : 00:48:22
Actually you can use sys.sysconstraints and join with sys.objects to list all constraints for all tables.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-02-24 : 00:48:28
quote:
Originally posted by divyaram

quote:
Originally posted by dinakar

Check out the sp_helpconstraint system proc.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/


Thank you Dinakar, buti want to list all default constarint at the same time .sp_help constraint we have give each table name.. i want list for whole database at one time

Regards,
Divya




Do you expect this??

select * from sys.objects where type='d'

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/
Go to Top of Page

divyaram
Posting Yak Master

180 Posts

Posted - 2010-02-24 : 01:35:14
quote:
Originally posted by senthil_nagore

quote:
Originally posted by divyaram

quote:
Originally posted by dinakar

Check out the sp_helpconstraint system proc.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/


Thank you Dinakar, buti want to list all default constarint at the same time .sp_help constraint we have give each table name.. i want list for whole database at one time

Regards,
Divya




Do you expect this??

select * from sys.objects where type='d'

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/




hi Senthil.C,


it worked .. thank you.... one more column i have to retrive the refernce column of default constraint i have done like this but its nt wrking ...
the code which i have done ....
SELECT
OBJECT_NAME(sys.objects.OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName
--,OBJECT_NAME(Sys.Columns.name) as Referenced_Column_Name
FROM sys.objects
--INNER JOIN Sys.Columns ON Sys.Objects.object_id = Sys.Columns.object_id
WHERE sys.objects.type_desc LIKE '%CONSTRAINT' and type='d'
order by TableName


it nt wrking so i have commended for Sys.Columns

Regards,
Divya
Go to Top of Page

divyaram
Posting Yak Master

180 Posts

Posted - 2010-02-24 : 02:08:14
how to inner join sys.column with sys.object...

Regards,
Divya
Go to Top of Page

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-24 : 04:36:53
Try this...


SELECT
OBJECT_NAME(sys.objects.OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName
,Sys.Columns.name as Referenced_Column_Name
FROM sys.objects
INNER JOIN Sys.Columns ON Sys.Objects.object_id = Sys.Columns.default_object_id
WHERE sys.objects.type_desc LIKE '%CONSTRAINT' and type='d'
order by TableName

Vabhav T
Go to Top of Page

divyaram
Posting Yak Master

180 Posts

Posted - 2010-02-24 : 04:53:32
quote:
Originally posted by vaibhavktiwari83

Try this...


SELECT
OBJECT_NAME(sys.objects.OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName
,Sys.Columns.name as Referenced_Column_Name
FROM sys.objects
INNER JOIN Sys.Columns ON Sys.Objects.object_id = Sys.Columns.default_object_id
WHERE sys.objects.type_desc LIKE '%CONSTRAINT' and type='d'
order by TableName

Vabhav T




Thank you vabhav it worked ........

Regards,
Divya
Go to Top of Page

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-24 : 04:54:50
You'r welcome....

Vabhav T
Go to Top of Page
   

- Advertisement -