SQL Server Performance Forum – Threads Archive
How to Query a tables Constraint rules
I am working in an environment where I only have read access to a replication of production (SS2K5), however I am responsible for writing scripts to update/insert delete data on a rare occasion. I need to be able to submit a script to some one that can execute it that will tell me the exact rules of the constraints of a table so I can make sure I don’t violate any table constraints with an update/insert script. Anyone have any ideas on how to do this?<br /><br />If I could just read the production database everything would be so much easier but for now this is what I have to deal with. I consider it a challenge since it makes everything so difficult. [<img src=’/community/emoticons/emotion-2.gif’ alt=’
‘ />]<br /><br />Any help would be greatly appreciated.
Here is the script, you modify as needed… SELECT fk_tc.TABLE_NAME,
fk_tc.CONSTRAINT_NAME,
pk_tc.TABLE_NAME,
pk_tc.CONSTRAINT_NAME
FROM Information_Schema.Table_Constraints as fk_tc
JOIN Information_Schema.Referential_Constraints as rc
on fk_tc.Constraint_Name = rc.Constraint_Name
JOIN Information_Schema.Table_Constraints as pk_tc
on rc.UNIQUE_CONSTRAINT_NAME = pk_tc.CONSTRAINT_NAME
order by
fk_tc.TABLE_NAME,
fk_tc.CONSTRAINT_NAME,
pk_tc.TABLE_NAME,
pk_tc.CONSTRAINT_NAME
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
check out the sysforeignkeys table. You can probably find all the info you need. ***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/
sysforeignkeys give only ids but not the names but in 2005 you can use sys.foreign_keys which will give additional info… select * from sys.foreign_keys MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Never heard of OBJECT_NAME(<id><img src=’/community/emoticons/emotion-5.gif’ alt=’
‘ />?
These scripts give me the name of the primary/foreign keys but don’t give me the exact rules enforced by those constraints. What I am looking for is a way to script the following actions: In SSMS select the table and right click
Choose Script Table As
Choose Create To
Choose File This gives me all of the constraints enforced upon a table including the primary/foreign key constraints. Thanks for your help!
Why dont you run profiler and do the same thing you mentioned and see what the DB does and you can write your own proc with that code or call the same procs that the DB is calling. you will learn more this way. ***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/
That is a good idea, it’s a good thing I have SS2K5 installed at home. I’ve been looking for an excuse to test out Profiler! Ill post my findings as soon as I can, thanks for the suggestion.
]]>