Finding Schema Relationship Constraints in Sys Tables

[Ovatvvon]

Hi All,

General Description
I have a support table, which holds ID values.
For instance, descriptions of member types:

id | code_type
==================
 1 | Lead
 2 | Member
 3 | Inactive

...etc
==================

There are three other tables in the database which have relationships (i.e. foreign keys) pointing to the ID field in this table, which is the primary key.

What I've tried
When I run the query:

select * from INFORMATION_SCHEMA.Referential_constraints

I get a list of constraint names in the database, but not the tables involved. (The names are not always clear in included the table names, plus I want the names to be a field, so I can select them to use them in a script file)

If I run the query:

select * from sys.all_objects
where type = 'F'

Then I also get Foreign Keys in the database, and I can then pull the parent_id field to get the table it belongs to...which is closer to what I want.

The Problem
I need all tables that are pointing to this support table (called "codes"), but since this is the primary source, the other tables are pointing to this. Since I won't necessarily know all the tables that are pointing to this, or any other given table in a similar situation...

Question
Does anyone know of any system tables I can use to query and find out all the other tables who are currently pointing a relationship to the one I'm querying on?

So for instance, if I take the codes table (listed above), and query it, then it should list 3 relationships pointing to it:
1) From Object ID number / Table name, column name, to this Codes table.
2) From another Object ID number / Table name, column name, to this Codes table,
...etc.

Then I can just run a drop statement to get rid of the three, before continuing other operations with schema changes.

(Hope this question made sense.)


-Ovatvvon :-Q

 

[Ovatvvon]

Nevermind, I found the answer:

select * from sys.foreign_keys
where referenced_object_id = OBJECT_ID('[i]name_of_table[/i]')

Star for me, yay!

-Ovatvvon :-Q