How to list all foreign keys referencing a given table in SQL Server?

How to list all foreign keys referencing a given table in SQL Server?

To list all foreign keys referencing a specific table in SQL Server, you can query the system catalog views. Here's how you can do it:

Using SQL Server Management Studio (SSMS):

  1. Connect to your SQL Server instance using SQL Server Management Studio.

  2. Open a New Query window.

  3. Run the following query to retrieve information about foreign keys referencing your table (YourTableName):

SELECT fk.name AS ForeignKeyName, OBJECT_NAME(fk.parent_object_id) AS TableName, COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS ColumnName, OBJECT_NAME(fk.referenced_object_id) AS ReferencedTableName, COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS ReferencedColumnName FROM sys.foreign_keys AS fk INNER JOIN sys.foreign_key_columns AS fkc ON fk.object_id = fkc.constraint_object_id WHERE OBJECT_NAME(fk.referenced_object_id) = 'YourTableName' ORDER BY TableName, ForeignKeyName;

Explanation:

  • sys.foreign_keys: This system view contains metadata about all foreign keys defined in the database.
  • sys.foreign_key_columns: This view provides details about the columns involved in each foreign key relationship.
  • OBJECT_NAME() and COL_NAME(): These functions are used to retrieve the names of the referenced and referencing tables, as well as the corresponding column names.

Adjustments:

  • Replace 'YourTableName' with the name of the table you want to find foreign keys referencing.
  • The query retrieves details such as the name of the foreign key (ForeignKeyName), the table containing the foreign key (TableName), the column in the referencing table (ColumnName), the referenced table (ReferencedTableName), and the referenced column (ReferencedColumnName).

Example:

If you want to find all foreign keys referencing the table Employees, you would run:

SELECT fk.name AS ForeignKeyName, OBJECT_NAME(fk.parent_object_id) AS TableName, COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS ColumnName, OBJECT_NAME(fk.referenced_object_id) AS ReferencedTableName, COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS ReferencedColumnName FROM sys.foreign_keys AS fk INNER JOIN sys.foreign_key_columns AS fkc ON fk.object_id = fkc.constraint_object_id WHERE OBJECT_NAME(fk.referenced_object_id) = 'Employees' ORDER BY TableName, ForeignKeyName;

This query will list all foreign keys that reference the Employees table, along with the details of the referencing table, columns, and the referenced table and columns.

By using these system views and functions, you can efficiently retrieve information about foreign keys referencing any table in your SQL Server database. Adjust the query as per your specific requirements and database schema.

Examples

  1. List all foreign keys that reference a specific table in SQL Server:

    • Description: This query helps find all foreign keys in a SQL Server database that reference a particular table, referenced_table_name.
    • Code:
      SELECT FK.name AS ForeignKey, TP.name AS ReferencingTable, AC.name AS ReferencingColumn, RT.name AS ReferencedTable, RC.name AS ReferencedColumn FROM sys.foreign_keys AS FK INNER JOIN sys.tables AS TP ON FK.parent_object_id = TP.object_id INNER JOIN sys.tables AS RT ON FK.referenced_object_id = RT.object_id INNER JOIN sys.foreign_key_columns AS FKC ON FKC.constraint_object_id = FK.object_id INNER JOIN sys.columns AS AC ON FKC.parent_column_id = AC.column_id AND FKC.parent_object_id = AC.object_id INNER JOIN sys.columns AS RC ON FKC.referenced_column_id = RC.column_id AND FKC.referenced_object_id = RC.object_id WHERE RT.name = 'referenced_table_name';

  2. Find all foreign keys that reference a table with a specific schema in SQL Server:

    • Description: This query retrieves foreign keys from tables in a SQL Server database that reference a table named referenced_table_name in a specific schema.
    • Code:
      SELECT FK.name AS ForeignKey, TP.name AS ReferencingTable, AC.name AS ReferencingColumn, RT.name AS ReferencedTable, RC.name AS ReferencedColumn FROM sys.foreign_keys AS FK INNER JOIN sys.tables AS TP ON FK.parent_object_id = TP.object_id INNER JOIN sys.tables AS RT ON FK.referenced_object_id = RT.object_id INNER JOIN sys.foreign_key_columns AS FKC ON FKC.constraint_object_id = FK.object_id INNER JOIN sys.columns AS AC ON FKC.parent_column_id = AC.column_id AND FKC.parent_object_id = AC.object_id INNER JOIN sys.columns AS RC ON FKC.referenced_column_id = RC.column_id AND FKC.referenced_object_id = RC.object_id WHERE RT.name = 'referenced_table_name' AND RT.schema_id = SCHEMA_ID('schema_name');

  3. List all foreign keys referencing any table in the database with specific column names in SQL Server:

    • Description: This query identifies foreign keys in a SQL Server database that reference any table, focusing on specific column names, referenced_column_name.
    • Code:
      SELECT FK.name AS ForeignKey, TP.name AS ReferencingTable, AC.name AS ReferencingColumn, RT.name AS ReferencedTable, RC.name AS ReferencedColumn FROM sys.foreign_keys AS FK INNER JOIN sys.tables AS TP ON FK.parent_object_id = TP.object_id INNER JOIN sys.tables AS RT ON FK.referenced_object_id = RT.object_id INNER JOIN sys.foreign_key_columns AS FKC ON FKC.constraint_object_id = FK.object_id INNER JOIN sys.columns AS AC ON FKC.parent_column_id = AC.column_id AND FKC.parent_object_id = AC.object_id INNER JOIN sys.columns AS RC ON FKC.referenced_column_id = RC.column_id AND FKC.referenced_object_id = RC.object_id WHERE RC.name = 'referenced_column_name';

  4. Find all foreign keys referencing a specific table column in SQL Server:

    • Description: This query retrieves foreign keys in a SQL Server database that reference a specific column, referenced_column_name, in any table.
    • Code:
      SELECT FK.name AS ForeignKey, TP.name AS ReferencingTable, AC.name AS ReferencingColumn, RT.name AS ReferencedTable, RC.name AS ReferencedColumn FROM sys.foreign_keys AS FK INNER JOIN sys.tables AS TP ON FK.parent_object_id = TP.object_id INNER JOIN sys.tables AS RT ON FK.referenced_object_id = RT.object_id INNER JOIN sys.foreign_key_columns AS FKC ON FKC.constraint_object_id = FK.object_id INNER JOIN sys.columns AS AC ON FKC.parent_column_id = AC.column_id AND FKC.parent_object_id = AC.object_id INNER JOIN sys.columns AS RC ON FKC.referenced_column_id = RC.column_id AND FKC.referenced_object_id = RC.object_id WHERE RC.name = 'referenced_column_name';

  5. List all foreign keys that reference a specific table in a SQL Server database with details:

    • Description: This query provides detailed information about foreign keys in a SQL Server database that reference referenced_table_name.
    • Code:
      SELECT FK.name AS ForeignKeyName, TP.name AS ReferencingTableName, AC.name AS ReferencingColumnName, RT.name AS ReferencedTableName, RC.name AS ReferencedColumnName, FK.delete_referential_action_desc AS DeleteAction, FK.update_referential_action_desc AS UpdateAction FROM sys.foreign_keys AS FK INNER JOIN sys.tables AS TP ON FK.parent_object_id = TP.object_id INNER JOIN sys.tables AS RT ON FK.referenced_object_id = RT.object_id INNER JOIN sys.foreign_key_columns AS FKC ON FKC.constraint_object_id = FK.object_id INNER JOIN sys.columns AS AC ON FKC.parent_column_id = AC.column_id AND FKC.parent_object_id = AC.object_id INNER JOIN sys.columns AS RC ON FKC.referenced_column_id = RC.column_id AND FKC.referenced_object_id = RC.object_id WHERE RT.name = 'referenced_table_name';

  6. Find all foreign keys referencing a specific table with ON DELETE CASCADE action in SQL Server:

    • Description: This query identifies foreign keys in a SQL Server database that reference referenced_table_name with ON DELETE CASCADE action.
    • Code:
      SELECT FK.name AS ForeignKey, TP.name AS ReferencingTable, AC.name AS ReferencingColumn, RT.name AS ReferencedTable, RC.name AS ReferencedColumn FROM sys.foreign_keys AS FK INNER JOIN sys.tables AS TP ON FK.parent_object_id = TP.object_id INNER JOIN sys.tables AS RT ON FK.referenced_object_id = RT.object_id INNER JOIN sys.foreign_key_columns AS FKC ON FKC.constraint_object_id = FK.object_id INNER JOIN sys.columns AS AC ON FKC.parent_column_id = AC.column_id AND FKC.parent_object_id = AC.object_id INNER JOIN sys.columns AS RC ON FKC.referenced_column_id = RC.column_id AND FKC.referenced_object_id = RC.object_id WHERE RT.name = 'referenced_table_name' AND FK.delete_referential_action_desc = 'CASCADE';

  7. List all foreign keys referencing tables in a specific schema in SQL Server:

    • Description: This query retrieves foreign keys in a SQL Server database that reference tables in a specific schema, schema_name.
    • Code:
      SELECT FK.name AS ForeignKey, TP.name AS ReferencingTable, AC.name AS ReferencingColumn, RT.name AS ReferencedTable, RC.name AS ReferencedColumn FROM sys.foreign_keys AS FK INNER JOIN sys.tables AS TP ON FK.parent_object_id = TP.object_id INNER JOIN sys.tables AS RT ON FK.referenced_object_id = RT.object_id INNER JOIN sys.foreign_key_columns AS FKC ON FKC.constraint_object_id = FK.object_id INNER JOIN sys.columns AS AC ON FKC.parent_column_id = AC.column_id AND FKC.parent_object_id = AC.object_id INNER JOIN sys.columns AS RC ON FKC.referenced_column_id = RC.column_id AND FKC.referenced_object_id = RC.object_id WHERE RT.schema_id = SCHEMA_ID('schema_name');

  8. Find all foreign keys referencing a table with specific column data type in SQL Server:

    • Description: This query identifies foreign keys in a SQL Server database that reference a table with a specific data type for columns.
    • Code:
      SELECT FK.name AS ForeignKey, TP.name AS ReferencingTable, AC.name AS ReferencingColumn, RT.name AS ReferencedTable, RC.name AS ReferencedColumn FROM sys.foreign_keys AS FK INNER JOIN sys.tables AS TP ON FK.parent_object_id = TP.object_id INNER JOIN sys.tables AS RT ON FK.referenced_object_id = RT.object_id INNER JOIN sys.foreign_key_columns AS FKC ON FKC.constraint_object_id = FK.object_id INNER JOIN sys.columns AS AC ON FKC.parent_column_id = AC.column_id AND FKC.parent_object_id = AC.object_id INNER JOIN sys.columns AS RC ON FKC.referenced_column_id = RC.column_id AND FKC.referenced_object_id = RC.object_id WHERE RC.system_type_id = (SELECT system_type_id FROM sys.columns WHERE object_id = OBJECT_ID('referenced_table_name') AND name = 'referenced_column_name');

  9. List all foreign keys that reference a table and column with a specific constraint in SQL Server:

    • Description: This query retrieves foreign keys in a SQL Server database that reference a specific table and column with a defined constraint.
    • Code:
      SELECT FK.name AS ForeignKey, TP.name AS ReferencingTable, AC.name AS ReferencingColumn, RT.name AS ReferencedTable, RC.name AS ReferencedColumn FROM sys.foreign_keys AS FK INNER JOIN sys.tables AS TP ON FK.parent_object_id = TP.object_id INNER JOIN sys.tables AS RT ON FK.referenced_object_id = RT.object_id INNER JOIN sys.foreign_key_columns AS FKC ON FKC.constraint_object_id = FK.object_id INNER JOIN sys.columns AS AC ON FKC.parent_column_id = AC.column_id AND FKC.parent_object_id = AC.object_id INNER JOIN sys.columns AS RC ON FKC.referenced_column_id = RC.column_id AND FKC.referenced_object_id = RC.object_id WHERE RT.name = 'referenced_table_name' AND RC.name = 'referenced_column_name' AND RC.is_sparse = 0;

  10. Find all foreign keys referencing a specific table column with enabled state in SQL Server:

    • Description: This query identifies foreign keys in a SQL Server database that reference a specific column in referenced_table_name with an enabled state.
    • Code:
      SELECT FK.name AS ForeignKey, TP.name AS ReferencingTable, AC.name AS ReferencingColumn, RT.name AS ReferencedTable, RC.name AS ReferencedColumn FROM sys.foreign_keys AS FK INNER JOIN sys.tables AS TP ON FK.parent_object_id = TP.object_id INNER JOIN sys.tables AS RT ON FK.referenced_object_id = RT.object_id INNER JOIN sys.foreign_key_columns AS FKC ON FKC.constraint_object_id = FK.object_id INNER JOIN sys.columns AS AC ON FKC.parent_column_id = AC.column_id AND FKC.parent_object_id = AC.object_id INNER JOIN sys.columns AS RC ON FKC.referenced_column_id = RC.column_id AND FKC.referenced_object_id = RC.object_id WHERE RT.name = 'referenced_table_name' AND FK.is_disabled = 0;

More Tags

increment flask uitapgesturerecognizer automapper navigator linefeed message office365-restapi size fencepost

More Programming Questions

More Physical chemistry Calculators

More Tax and Salary Calculators

More Weather Calculators

More General chemistry Calculators