sql - 显示表之间的关系类型

Question

我是 SQL 新手，想创建一个脚本来显示两个参数表之间的关系类型（M:N、1:M、M:1 和 1:1）。理想情况下，它将接受表名、第一个表的 ID、第二个表的 ID，然后显示它们之间的关系。我做了很多搜索，但大多数答案只关注通常不存在的 FK/PK 关系。示例：员工 ID、部门 ID。员工与部门之间的关系为 M:1。我使用 SQL Server，但任何 SQL 都应该没问题。

标签： sqlsql-servertsql

Answer 1

我已经修补了这件事。我已经很接近了。此代码可以使用一些改进。当然，在某些情况下，它不能容纳复合键。所以我提出这个作为一个起点。

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;

因此，对于您的员工/部门关系，您将使用这些值运行它......

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

由于您没有 PK，您只需要硬编码@Table1PK和@Table2PK值。