How to find all foreign keys?

Question

I'd like to find all referencing tables in my db that have a foreign key that points to a specific referenced table. Is there a query that I can run to do this?

Not sure if the question is confusing. Let me know if it is and I can try to explain it in more detail.

Answer 1

Have a look at the metadata:

SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE

Answer 2

S

ELECT  DISTINCT
         ParentSchema.name      ParentSchema
        ,ParentTable.name       ParentTable
        ,ChildSchema.name       ChildSchema
        ,ChildTable.name        ChildTable
FROM sys.foreign_keys       Foreign_Keys
JOIN sys.objects            ParentTable     ON  Foreign_Keys.parent_object_id       =   ParentTable.object_id
JOIN sys.schemas            ParentSchema    ON  ParentTable.schema_id               =   ParentSchema.schema_id
JOIN sys.objects            ChildTable      ON  Foreign_Keys.referenced_object_id   =   ChildTable.object_id
JOIN sys.schemas            ChildSchema     ON  ChildTable.schema_id                =   ChildSchema.schema_id
WHERE ParentSchema.name = '??????'
  AND ParentTable.name = '??????'

Answer 3

The following query or Modification tehreof will do - in Sql server You can also supply catalog and schema info

select tab1.TABLE_NAME from 
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as ref inner join
INFORMATION_SCHEMA.TABLE_CONSTRAINTS as prim
on ref.UNIQUE_CONSTRAINT_NAME=prim.CONSTRAINT_NAME
and ref.UNIQUE_CONSTRAINT_CATALOG=prim.CONSTRAINT_CATALOG
and ref.UNIQUE_CONSTRAINT_SCHEMA=prim.CONSTRAINT_SCHEMA
--and prim.CONSTRAINT_TYPE in('PRIMARY KEY','UNIQUE')
inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS tab1 on
ref.CONSTRAINT_NAME=tab1.CONSTRAINT_NAME
and ref.CONSTRAINT_CATALOG=tab1.CONSTRAINT_CATALOG
and ref.CONSTRAINT_SCHEMA=tab1.CONSTRAINT_SCHEMA
where prim.TABLE_NAME='YourTablename'

Answer 4

I had a similar problem some time ago. Here is a script I wrote using the Sql Server SMO:

    public static string GetForeignKeyScript()
    {
        SqlConnection conn = new System.Data.SqlClient.SqlConnection("SOME_CONNECTION_STRING");
        Server server = new Server(new ServerConnection(conn));
        Database db = server.Databases["SOME_DATABASE"];
        Table Roles = db.Tables["SOME_TABLE"];

        var sb = new StringBuilder();

        foreach (Table table in db.Tables)
            foreach (ForeignKey fk in table.ForeignKeys)
                    foreach (string s in fk.Script())
                        sb.AppendLine(s);

        return sb.ToString();
    }

This will output a string containing the scripts for creating the foreign keys.

Edited the code to remove stuff that was fairly specific to my project (I was only interested in tables that ended in a certain string for instance)