Display Relationship Types between tables

Question

I'm new to SQL and wanted to create a script that shows me the relationship type (M:N, 1:M, M:1, and 1:1) between two param tables. Ideally, it would accept table name, ID(s) for first table, ID(s) for second table, and then display the relationship between them. I did a lot of search, but most answers only focus on FK/PK relations that often don't exist. An example: employeeID, departmentID. And relation between employee and department would be M:1. I use SQL Server, but any SQL should be fine.

Answer 1

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.