How to find foreign key dependencies in SQL Server?

by

The following query will help to get you started. It lists all Foreign Key Relationships within the current database.

SELECT
    FK_Table = FK.TABLE_NAME,
    FK_Column = CU.COLUMN_NAME,
    PK_Table = PK.TABLE_NAME,
    PK_Column = PT.COLUMN_NAME,
    Constraint_Name = C.CONSTRAINT_NAME
FROM
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
    ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
    ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
    ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
            SELECT
                i1.TABLE_NAME,
                i2.COLUMN_NAME
            FROM
                INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
                ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
            WHERE
                i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
           ) PT
    ON PT.TABLE_NAME = PK.TABLE_NAME

You can also view relationships graphically within SQL Server Management studio within Database Diagrams.

More Related Contents:

  1. Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths – why?
  2. How do you effectively model inheritance in a database?
  3. How does SQLParameter prevent SQL Injection?
  4. Like Operator in Entity Framework?
  5. Why did a network-related or instance-specific error occur while establishing a connection to SQL Server?
  6. What is the correct SQL type to store a .Net Timespan with values > 24:00:00?
  7. How can I solve a connection pool problem between ASP.NET and SQL Server?
  8. Login failed for user ‘DOMAIN\MACHINENAME$’
  9. Update primary key value using entity framework
  10. A connection was successfully established with the server, but then an error occurred during the pre-login handshake
  11. What’s the best method to pass parameters to SQLCommand?
  12. What’s a good way to trim all whitespace characters from a string in T-SQL without UDF and without CLR?
  13. SqlDateTime.MinValue != DateTime.MinValue, why?
  14. Using “GO” within a transaction
  15. How to solve “unable to switch the encoding” error when inserting XML into SQL Server
  16. CLR Strict Security on SQL Server 2017
  17. How to build RUNAS /NETONLY functionality into a (C#/.NET/WinForms) program?
  18. Procedure expects parameter which was not supplied
  19. Does End Using close an open SQL Connection
  20. Does this code prevent SQL injection?
  21. TSQL md5 hash different to C# .NET md5
  22. How do I write LINQ’s .Skip(1000).Take(100) in pure SQL?
  23. How can you name the Dataset’s Tables you return in a stored proc?
  24. How to I serialize a large graph of .NET object into a SQL Server BLOB without creating a large buffer?
  25. Unable to load DLL ‘SqlServerSpatial.dll’
  26. .NET, the SqlConnection object, and multi-threading
  27. Implementing transactions over multiple databases
  28. SQL Connection Error: System.Data.SqlClient.SqlException (0x80131904)
  29. Using a variable for table name in ‘From’ clause in SQL Server 2008
  30. How to serialize a large graph of .NET objects into a SQL Server BLOB without creating a large buffer?

Leave a Comment