显示表之间的关系类型

Question

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

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值。