Author |
Topic |
divyaram
Posting Yak Master
180 Posts |
Posted - 2010-02-23 : 23:53:04
|
Hi allHow to get the all constraints in a database... i got the code for selecting foreignkey constraints of a database , i want to list all constraints in a databasethe code which we will get the foreignkey constraints isselecto1.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_namefrom sysforeignkeys fkinner join sysobjects o1 on fk.fkeyid = o1.idinner join sysobjects o2 on fk.rkeyid = o2.idinner join syscolumns c1 on c1.id = o1.id and c1.colid = fk.fkeyinner join syscolumns c2 on c2.id = o2.id and c2.colid = fk.rkeyinner join sysobjects s on fk.constid = s.idorder by Referencing_Object_nameRegards,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/ |
|
|
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 timeRegards,Divya |
|
|
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/ |
|
|
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 timeRegards,Divya
Do you expect this??select * from sys.objects where type='d'Senthil.C------------------------------------------------------[Microsoft][ODBC SQL Server Driver]Operation canceledhttp://senthilnagore.blogspot.com/ |
|
|
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 timeRegards,Divya
Do you expect this??select * from sys.objects where type='d'Senthil.C------------------------------------------------------[Microsoft][ODBC SQL Server Driver]Operation canceledhttp://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_NameFROM sys.objects --INNER JOIN Sys.Columns ON Sys.Objects.object_id = Sys.Columns.object_idWHERE sys.objects.type_desc LIKE '%CONSTRAINT' and type='d' order by TableNameit nt wrking so i have commended for Sys.ColumnsRegards,Divya |
|
|
divyaram
Posting Yak Master
180 Posts |
Posted - 2010-02-24 : 02:08:14
|
how to inner join sys.column with sys.object...Regards,Divya |
|
|
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_NameFROM sys.objectsINNER JOIN Sys.Columns ON Sys.Objects.object_id = Sys.Columns.default_object_idWHERE sys.objects.type_desc LIKE '%CONSTRAINT' and type='d' order by TableNameVabhav T |
|
|
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_NameFROM sys.objectsINNER JOIN Sys.Columns ON Sys.Objects.object_id = Sys.Columns.default_object_idWHERE sys.objects.type_desc LIKE '%CONSTRAINT' and type='d' order by TableNameVabhav T
Thank you vabhav it worked ........Regards,Divya |
|
|
vaibhavktiwari83
Aged Yak Warrior
843 Posts |
Posted - 2010-02-24 : 04:54:50
|
You'r welcome....Vabhav T |
|
|
|