Know Relationships Between All the Tables of Database in SQL Server

Know relationships between all the tables of database in SQL Server

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.....

Way for Seeing the Table relationship Tree for a Database?

1. Open your SQL Server management studio and enter into your DB.

2. Enter into Database Diagrams and search for diagrams

3. If there is no diagram, create a new diagram (right mouse click - New Database Diagram)

4. Inside the Diagram use right mouse click, add all the relevant tables and see the relationships

   

You can see more info about DB Diagrams here

You can see here an example for diagrams with tables and relationships

List of all tables with a relationship to a given table or view

For SQL Server 2005 and up, use something like:

SELECT
    name, OBJECT_NAME(parent_object_id) 'Table'
FROM 
    sys.foreign_keys
WHERE 
    referenced_object_id = OBJECT_ID('Your-referenced-table-name-here')

How to find all the relations between all tables with INFORMATION_SCHEMA in SQL Server?

You need a bit more of those views to get what you need:

• List of FKs with both ends specified is in the INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
• Mapping between referenced key and its table name is in the INFORMATION_SCHEMA.TABLE_CONSTRAINTS

Something like this should work:

select cu.CONSTRAINT_NAME as [ForeignKeyName],
    cu.TABLE_SCHEMA as [ReferencingTableSchema],
    cu.TABLE_NAME as [ReferencingTableName],
    cu.COLUMN_NAME as [ReferencingColumnName],
    tc.TABLE_SCHEMA as [ReferencedTableSchema],
    tc.TABLE_NAME as [ReferencedTableName],
    tc.CONSTRAINT_TYPE,
    rc.UNIQUE_CONSTRAINT_NAME as [ReferencedKeyName]
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu
    inner join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc on rc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME
    inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc on tc.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME
        and tc.TABLE_SCHEMA = rc.UNIQUE_CONSTRAINT_SCHEMA
order by cu.TABLE_SCHEMA, cu.TABLE_NAME, cu.CONSTRAINT_NAME, cu.COLUMN_NAME;

The query is probably not 100% fool-proof, you need to check with multi-column FKs, and possibly relationships between tables in different schemas. Still, a good starting point.

Oh, and if you need a column name from the parent table, you will need the KEY_COLUMN_USAGE again, this time for the referenced table. Might be tricky with multi-column FKs, look at the ORDINAL_POSITION column, it should give you the correlation which child column references which parent one.

Display Relationship Types between tables

I have tinkered with this very thing. I got pretty close. This code could use some refinement. And there are certainly situations that it doesn't accommodate such as composite keys. So I offer this up as a starting point.

DECLARE
    @Table1           VARCHAR(35)
  , @Table1PK         VARCHAR(35)
  , @Table1JoinColumn VARCHAR(35)
  , @Table2           VARCHAR(35)
  , @Table2PK         VARCHAR(35)
  , @Table2JoinColumn VARCHAR(35)
  , @SQL              NVARCHAR(4000);
  
-------------------------------------------------------------------------------------
-- fill in these four variable values for you situation...
SET @Table1 =           'replace with Table1'
SET @Table1JoinColumn = 'replace with Table1 column to join to Table2'
SET @Table2 =           'replace with Table2'
SET @Table2JoinColumn = 'replace with Table2 column to join to Table1'
-------------------------------------------------------------------------------------

-- get the Primary Key column for Table1
SELECT     @Table1PK = c.name
FROM       sys.indexes       i
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id
                                   AND i.index_id = ic.index_id
INNER JOIN sys.columns       c ON ic.object_id = c.object_id
                                  AND c.column_id = ic.column_id
WHERE      i.is_primary_key = 1
           AND i.object_id = OBJECT_ID('dbo.' + @Table1);

-- get the Primary Key column for Table2
SELECT     @Table2PK = c.name
FROM       sys.indexes       i
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id
                                   AND i.index_id = ic.index_id
INNER JOIN sys.columns       c ON ic.object_id = c.object_id
                                  AND c.column_id = ic.column_id
WHERE      i.is_primary_key = 1
           AND i.object_id = OBJECT_ID('dbo.' + @Table2);

IF OBJECT_ID('tempdb.dbo.##TableCounts', 'U') IS NOT NULL
    DROP TABLE ##TableCounts;
            
SET @SQL = '
SELECT '''
          + @Table1 + ''' AS [T1] 
        , '''+ @Table2 + ''' AS [T2] 
        , COUNT(DISTINCT a.' + @Table1PK + ')    AS [T1 Count]
        , SUM(CASE
                 WHEN b.' + @Table2PK + ' IS NULL THEN
                     1
                 ELSE
                     0
              END
             )                                    AS [T1 - T2 Count]
        , SUM(CASE
                 WHEN a.' + @Table1PK + ' IS NOT NULL and b.' + @Table2PK + ' IS NOT NULL THEN
                     1
                 ELSE
                     0
             END
             )                                   AS [T1 ' + NCHAR(1352) + ' T2 Count]
        , SUM(CASE
                 WHEN a.' + @Table1PK + ' IS NULL THEN
                     1
                 ELSE
                     0
              END
             )                                   AS [T2 - T1 Count]
        , COUNT(DISTINCT b.' + @Table2PK + ')    AS [T2 Count]
INTO ##TableCounts
FROM      dbo.' + @Table1 + ' a
FULL JOIN dbo.' + @Table2 + '    b ON a.' + @Table1JoinColumn + ' = b.' + @Table2JoinColumn + ''

PRINT @SQL

EXEC sp_executesql @SQL

SELECT
     *
   , CASE
         WHEN [T1 Count] = [T2 Count]
              AND [T1 - T2 Count] = 0 AND [T2 - T1 Count] = 0 THEN
             '1-1'
         WHEN [T1 Count] > [T2 Count] THEN
             'n-1'
         WHEN [T1 Count] < [T2 Count] THEN
             '1-n'
         ELSE
             'n-n'
     END AS [Cardinality]
   , CASE
         WHEN [T1 - T2 Count] > 0 THEN
             'T1'
         WHEN [T2 - T1 Count] > 0 THEN
             'T2'
         ELSE
             'None'
     END [Outer Join]
FROM ##TableCounts;

So for your Employee/Department relationship you would run it with these values...

SET @Table1 =           'Employee'
SET @Table1JoinColumn = 'departmentID'
SET @Table2 =           'Department'
SET @Table2JoinColumn = 'departmentID'

Since you don't have PKs you would just have to hard code @Table1PK and @Table2PK values.

List sql table relations using query

Assuming there are foreign keys set up, you should be able to run this stored procedure.

EXEC sp_fkeys 'Employee'

---

Related Topics