☰

从脚本中的 JOINS 中发现预期的外键

tag-icon • tag-icon sql-server-2005 parsing foreign-key
从脚本中的 JOINS 中发现预期的外键

我继承了一个有 400 个表但只注册了 150 个外键约束的数据库。了解我对应用程序的操作并查看表列后，很容易看出应该还有更多。

如果我开始添加缺失的 FK，我担心当前的应用程序软件将会崩溃，因为开发人员可能已经开始依赖这种“自由”，但解决问题的第一步是列出缺失的 FK 列表，以便我们可以作为一个团队对它们进行评估。

更糟糕的是，引用列不共享命名约定。

这些关系被非正式地编码到数百个临时查询和存储过程中，所以我的希望是通过编程来解析这些文件，寻找实际表（但不是表变量等）之间的连接。

我预见到这种方法的挑战是：换行符，可选别名和表提示，别名解析。

  • 有更好的主意吗？（除了辞职）
  • 是否有任何预先构建的工具可以解决这个问题？
  • 我认为正则表达式无法处理这个问题。你不同意吗？
  • SQL 解析器？我尝试使用 Microsoft.SqlServer.Management.SqlParser.Parser，但所有暴露的都是词法分析器 - 无法从中获取 AST - 所有这些东西都是内部的。

答案1

强大的SQL Parser可以帮助自动分析数百个即席查询和存储过程，并且从SQL Parser生成的查询解析树中，您可以轻松找到变量表/列的关系。

以下是示例：

SELECT a.ASSMT_NO,
   b.LINK_PARAM,
   c.EXPL                                               AS LINK_PG,
   (SELECT count()
    FROM   GRAASPST t
    WHERE  t.ASSMT_NO = a.ASSMT_NO
           AND t.ROLE != '02')                          AS PSN_CNT,
   (SELECT count()
    FROM   GRAASPST t
    WHERE  t.ASSMT_NO = a.ASSMT_NO
           AND t.ROLE != '02'
           AND ASSMT_FIN_YN = 'Y')                      AS PSN_FIN_CNT,
   (SELECT Avg(assmt_pts)
    FROM   GRAASSMT t
    WHERE  t.ASSMT_NO = a.ASSMT_NO
           AND t.ASSMT_TGT_SEQ_NO = a.ASSMT_TGT_SEQ_NO) AS ASSMT_PTS_AVG,
   a.ASSMT_RES,
   a.ASSMT_RPT_SUB_TITLE
FROM   GRAASTAT a
   JOIN GRAASRET b
     ON b.DELIB_REQ_NO = a.DELIB_REQ_NO
   JOIN GRTCODDT c
     ON c.DIV_CD = 'GR013'
        AND c.CD = b.DELIB_SLCT
   JOIN CMUSERMT d
     ON d.USERID = a.REGID
WHERE  a.ASSMT_NO = :ASSMT_NO
ORDER  BY a.ASSMT_TGT_SEQ_NO

分析完这个查询之后，你可能会得到如下的结果：

JoinTable1         JoinColumn1       JoinTable2    JoinColumn2     
GRAASRET       DELIB_REQ_NO      GRAASTAT      DELIB_REQ_NO    
GRTCODDT       CD            GRAASRET      DELIB_SLCT      
CMUSERMT       USERID        GRAASTAT      REGID              
GRAASPST       ASSMT_NO      GRAASTAT      ASSMT_NO        
GRAASSMT       ASSMT_NO      GRAASTAT      ASSMT_NO        
GRAASSMT       ASSMT_TGT_SEQ_NO  GRAASTAT      ASSMT_TGT_SEQ_NO

您可以检查这个演示了解详细信息。

答案2

我感受到你的痛苦。

免费SQL 搜索SSMS Addin 可能对您有帮助。

总体而言，是的，正则表达式可以处理这个问题，但您应该意识到，尝试使用正则表达式魔法会降低回报。您可能最好只是在绘制关系的同时查看和搜索代码。

SQL 搜索可能会让这一切变得容易得多。

答案3

这就是我的想法。此查询查找外键类列（int、bigint、guid），这些列不是表的主键，并且当前未注册外键约束。当然，我得到了一些排序顺序和数量列，但它确实比解析 SQL 脚本更省力地缩小了列表范围。

WITH ExistingFKCs AS
(
    SELECT
        CU.TABLE_NAME, -- Referencing Table
        CU.COLUMN_NAME -- Referencing Column
    FROM 
        INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
        JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
)
SELECT 
    T.TABLE_NAME AS [Table Name], 
    COL.COLUMN_NAME AS [Column Name]
FROM 
    INFORMATION_SCHEMA.TABLES T 
    JOIN INFORMATION_SCHEMA.COLUMNS COL ON 
        T.TABLE_TYPE = 'BASE TABLE' AND
        COL.TABLE_NAME = T.TABLE_NAME
    LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PKC ON 
        PKC.CONSTRAINT_TYPE = 'Primary Key' AND 
        PKC.TABLE_NAME = COL.TABLE_NAME
    LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE PKCU ON 
        PKCU.TABLE_NAME = PKC.TABLE_NAME AND 
        PKCU.CONSTRAINT_NAME = PKC.CONSTRAINT_NAME AND
        PKCU.COLUMN_NAME = COL.COLUMN_NAME
    LEFT JOIN ExistingFKCs EFKS ON
        EFKS.TABLE_NAME = COL.TABLE_NAME AND
        EFKS.COLUMN_NAME = COL.COLUMN_NAME
WHERE 
    PKCU.COLUMN_NAME IS NULL
    AND EFKS.COLUMN_NAME IS NOT NULL
    AND COL.DATA_TYPE IN ('int', 'bigint','uniqueidentifier')
ORDER BY T.TABLE_NAME, COL.COLUMN_NAME

相关内容