finding foreign keys in ms sql server with information_schema

Question

I'm trying to find all the tables that have foreign keys pointing to a particular table. So I wrote this:

select t1.TABLE_NAME as pointsfrom, t2.TABLE_NAME as pointsto
        from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS r
        join INFORMATION_SCHEMA.TABLE_CONSTRAINTS t1 on t1.CONSTRAINT_NAME=r.CONSTRAINT_NAME
        join INFORMATION_SCHEMA.TABLE_CONSTRAINTS t2 on t2.CONSTRAINT_NAME=r.UNIQUE_CONSTRAINT_NAME
        where t2.table_name = @mytable

This works 90% of the time, or to be exact, for 22 of the 24 foreign keys in the database I'm working on. But for 2 FKs, the unique_constraint_name does not match any name in table_constraints.

The names are also a little funny. One of them is "environment designator", with a space. The field the FK points to is named "environment_designator", with an underscore. The other has a unique_constraint_name of "filename is unique", which doesn't correspond to anything I see in the definition of either the "from" or the "to" table.

Is there some place else I should look for a match on unique_constraint_name?

Answer 1

Try this:

SELECT OBJECT_NAME(parent_object_id), OBJECT_NAME(referenced_object_id)
    FROM sys.foreign_keys
    WHERE referenced_object_id = OBJECT_ID(@mytable)