How To list all foreign keys referencing In a given Table In SQL Server

I need to remove a highly referenced table in a SQL Server database. How can I get a list of all the foreign key constraints I will need to remove in order to drop the table?

Not sure why no one suggested but I use sp_fkeys to query foreign keys for a given table:

EXEC sp_fkeys 'TableName'

I'd use the Database Diagramming feature in SQL Server Management Studio, but since you ruled that out - this worked for me in SQL Server 2008.

To get list of referring table and column names:

select
    t.name as TableWithForeignKey,
    fk.constraint_column_id as FK_PartNo, c.
    name as ForeignKeyColumn
from
    sys.foreign_key_columns as fk
inner join
    sys.tables as t on fk.parent_object_id = t.object_id
inner join
    sys.columns as c on fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_id
where
    fk.referenced_object_id = (select object_id
                               from sys.tables
                               where name = 'TableOthersForeignKeyInto')
order by
    TableWithForeignKey, FK_PartNo
To get names of foreign key constraints

select distinct name from sys.objects where object_id in
(   select fk.constraint_object_id from sys.foreign_key_columns as fk
    where fk.referenced_object_id =
        (select object_id from sys.tables where name = 'TableOthersForeignKeyInto')
)

Try this :

sp_help 'TableName'

This gives you:

The FK itself itself
Schema that the FK belongs to
The "referencing table" or the table that has the FK
The "referencing column" or the column inside referencing table that points to the FK
The "referenced table" or the table that has the key column that your FK is pointing to
The "referenced column" or the column that is the key that your FK is pointing to
Code below:

SELECT  obj.name AS FK_NAME,
    sch.name AS [schema_name],
    tab1.name AS [table],
    col1.name AS [column],
    tab2.name AS [referenced_table],
    col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
    ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
    ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
    ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
    ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
    ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
    ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id

Determine primary keys and unique keys for all tables in a database:

This should list all the constraints and at the end you can put your filters

/* CAST IS DONE , SO THAT OUTPUT INTEXT FILE REMAINS WITH SCREEN LIMIT*/
WITH   ALL_KEYS_IN_TABLE (CONSTRAINT_NAME,CONSTRAINT_TYPE,PARENT_TABLE_NAME,PARENT_COL_NAME,PARENT_COL_NAME_DATA_TYPE,REFERENCE_TABLE_NAME,REFERENCE_COL_NAME)
AS
(
SELECT  CONSTRAINT_NAME= CAST (PKnUKEY.name AS VARCHAR(30)) ,
        CONSTRAINT_TYPE=CAST (PKnUKEY.type_desc AS VARCHAR(30)) ,
        PARENT_TABLE_NAME=CAST (PKnUTable.name AS VARCHAR(30)) ,
        PARENT_COL_NAME=CAST ( PKnUKEYCol.name AS VARCHAR(30)) ,
        PARENT_COL_NAME_DATA_TYPE=  oParentColDtl.DATA_TYPE,     
        REFERENCE_TABLE_NAME='' ,
        REFERENCE_COL_NAME=''

FROM sys.key_constraints as PKnUKEY
    INNER JOIN sys.tables as PKnUTable
            ON PKnUTable.object_id = PKnUKEY.parent_object_id
    INNER JOIN sys.index_columns as PKnUColIdx
            ON PKnUColIdx.object_id = PKnUTable.object_id
            AND PKnUColIdx.index_id = PKnUKEY.unique_index_id
    INNER JOIN sys.columns as PKnUKEYCol
            ON PKnUKEYCol.object_id = PKnUTable.object_id
            AND PKnUKEYCol.column_id = PKnUColIdx.column_id
     INNER JOIN INFORMATION_SCHEMA.COLUMNS oParentColDtl
            ON oParentColDtl.TABLE_NAME=PKnUTable.name
            AND oParentColDtl.COLUMN_NAME=PKnUKEYCol.name
UNION ALL
SELECT  CONSTRAINT_NAME= CAST (oConstraint.name AS VARCHAR(30)) ,
        CONSTRAINT_TYPE='FK',
        PARENT_TABLE_NAME=CAST (oParent.name AS VARCHAR(30)) ,
        PARENT_COL_NAME=CAST ( oParentCol.name AS VARCHAR(30)) ,
        PARENT_COL_NAME_DATA_TYPE= oParentColDtl.DATA_TYPE,   
        REFERENCE_TABLE_NAME=CAST ( oReference.name AS VARCHAR(30)) ,
        REFERENCE_COL_NAME=CAST (oReferenceCol.name AS VARCHAR(30))
FROM sys.foreign_key_columns FKC
    INNER JOIN sys.sysobjects oConstraint
            ON FKC.constraint_object_id=oConstraint.id
    INNER JOIN sys.sysobjects oParent
            ON FKC.parent_object_id=oParent.id
    INNER JOIN sys.all_columns oParentCol
            ON FKC.parent_object_id=oParentCol.object_id /* ID of the object to which this column belongs.*/
            AND FKC.parent_column_id=oParentCol.column_id/* ID of the column. Is unique within the object.Column IDs might not be sequential.*/
    INNER JOIN sys.sysobjects oReference
            ON FKC.referenced_object_id=oReference.id
    INNER JOIN INFORMATION_SCHEMA.COLUMNS oParentColDtl
            ON oParentColDtl.TABLE_NAME=oParent.name
            AND oParentColDtl.COLUMN_NAME=oParentCol.name
    INNER JOIN sys.all_columns oReferenceCol
            ON FKC.referenced_object_id=oReferenceCol.object_id /* ID of the object to which this column belongs.*/
            AND FKC.referenced_column_id=oReferenceCol.column_id/* ID of the column. Is unique within the object.Column IDs might not be sequential.*/

)

select * from   ALL_KEYS_IN_TABLE
where 
    PARENT_TABLE_NAME  in ('YOUR_TABLE_NAME')
    or REFERENCE_TABLE_NAME  in ('YOUR_TABLE_NAME')
ORDER BY PARENT_TABLE_NAME,CONSTRAINT_NAME;.