Know relationships between all the tables of database in SQL Server

by

Sometimes, a textual representation might also help; with this query on the system catalog views, you can get a list of all FK relationships and how the link two tables (and what columns they operate on).

SELECT
    fk.name 'FK Name',
    tp.name 'Parent table',
    cp.name, cp.column_id,
    tr.name 'Refrenced table',
    cr.name, cr.column_id
FROM 
    sys.foreign_keys fk
INNER JOIN 
    sys.tables tp ON fk.parent_object_id = tp.object_id
INNER JOIN 
    sys.tables tr ON fk.referenced_object_id = tr.object_id
INNER JOIN 
    sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN 
    sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id
INNER JOIN 
    sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id
ORDER BY
    tp.name, cp.column_id

Dump this into Excel, and you can slice and dice – based on the parent table, the referenced table or anything else.

I find visual guides helpful – but sometimes, textual documentation is just as good (or even better) – just my 2 cents…..

More Related Contents:

  1. Generate a resultset of incrementing dates in TSQL
  2. T-SQL Cast versus Convert
  3. How do I create a step in my SQL Server Agent Job which will run my SSIS package?
  4. What is the difference between using a cross join and putting a comma between the two tables?
  5. What does =* mean?
  6. Correct use of transactions in SQL Server
  7. Why do you create a View in a database?
  8. Need to list all triggers in SQL Server database with table name and table’s schema
  9. SQL Server search using like while ignoring blank spaces
  10. Convert text to system date format [duplicate]
  11. How to get a particular preceding value of a column in SQL [closed]
  12. Simple way to transpose columns and rows in SQL?
  13. What is the meaning of the prefix N in T-SQL statements and when should I use it?
  14. Maximum size for a SQL Server Query? IN clause? Is there a Better Approach [duplicate]
  15. SQL JOIN vs IN performance?
  16. Most efficient way in SQL Server to get date from date+time?
  17. Convert varchar into datetime in SQL Server
  18. TSQL Pivot without aggregate function
  19. How to generate a range of dates in SQL Server
  20. SQL RANK() versus ROW_NUMBER()
  21. Generate SQL Create Scripts for existing tables with Query
  22. Check if a row exists, otherwise insert
  23. Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement
  24. How do I perform a GROUP BY on an aliased column in SQL Server?
  25. How do I remove extended ASCII characters from a string in T-SQL?
  26. How to sum up time field in SQL Server
  27. SQL Server equivalent to MySQL enum data type?
  28. SQL – Call Stored Procedure for each record
  29. Why is RAND() not producing random numbers?
  30. Should every SQL Server foreign key have a matching index? [closed]

Leave a Comment