How can I list all foreign keys in a Table?

Question

Suppose, I know the name of a Table TableName1 in the Database Database1.

I want to find:

1. the foreign keys that table has.
2. what foreign tables they are referencing.
3. what foreign columns they are referencing.
4. the types, sizes, etc. of those foreign columns.

Is there any sql server query (not stored procedure) available for that?

Note:

The answer, "How can I list all foreign keys referencing a given table in SQL Server?" doesn't fulfill my requirement, as they accepted answer is a stored procedure, and the rest of them returns no or less results.

For instance, DailySale table has two foreign keys,

but this query

EXEC sp_fkeys 'TableName'

lists only one.

Answer 1

The below code snippet could be put to some use and modified by your further requirement - This would pull the column level details such as data type, length, precision, scale etc...

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],
    TY.[name] AS system_data_type, col1.[max_length],col1.[precision], col1.[scale], col1.[is_nullable], col1.[is_ansi_padded]
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
INNER JOIN sys.[types] TY ON col1.[system_type_id] = TY.[system_type_id] AND col1.[user_type_id] = TY.[user_type_id] 
AND tab1.name = 'TableName1'

Answer 2

You can switch the OBJECT_NAME(f.referenced_object_id) = 'tblpatientmaster' with OBJECT_NAME(f.parent_object_id) = 'tblpatientmaster' to get reference of this table to refer this table.

--Reference in this table
     SELECT
        f.name constraint_name
       ,OBJECT_NAME(f.parent_object_id) referencing_table_name
       ,COL_NAME(fc.parent_object_id, fc.parent_column_id) referencing_column_name
       ,OBJECT_NAME (f.referenced_object_id) referenced_table_name
       ,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) referenced_column_name

       , DATA_TYPE column_data_type
    FROM sys.foreign_keys AS f
    INNER JOIN sys.foreign_key_columns AS fc
       ON f.object_id = fc.constraint_object_id
    INNER JOIN INFORMATION_SCHEMA.COLUMNS col 
       ON COL_NAME(fc.parent_object_id, fc.parent_column_id) = col.COLUMN_NAME 
    where OBJECT_NAME(f.referenced_object_id) = 'YouTableName'
    ORDER BY f.name

-- Refer this table to other table

SELECT distinct
    f.name constraint_name
   ,OBJECT_NAME(f.parent_object_id) referencing_table_name
   ,COL_NAME(fc.parent_object_id, fc.parent_column_id) referencing_column_name
   ,OBJECT_NAME (f.referenced_object_id) referenced_table_name
   ,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) referenced_column_name

   , DATA_TYPE column_data_type
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
   ON f.object_id = fc.constraint_object_id
INNER JOIN INFORMATION_SCHEMA.COLUMNS col 
   ON COL_NAME(fc.parent_object_id, fc.parent_column_id) = col.COLUMN_NAME 
where OBJECT_NAME(f.parent_object_id) = 'YouTableName'
ORDER BY f.name