主页 / server / 问题 / 404161
Accepted
Jason Kleban
Asked: 2012-07-03 11:43:48 +0800 CST

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

  • 772

我继承了一个有 400 个表并且只注册了 150 个外键约束的数据库。了解我对应用程序所做的工作并查看表格列，很容易说应该有更多。

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

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

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

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

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

3 个回答

  1. 我感觉到你的痛苦。

    免费的SQL 搜索SSMS 插件可能对您有所帮助。

    一般来说，是的，正则表达式可以处理这个问题，但您应该意识到尝试使用正则表达式魔法时收益递减的点。在映射关系的同时检查和搜索代码可能会更好。

    SQL 搜索可能会让您更轻松地完成这项工作。

    • 4

  2. 这是我想出的。此查询查找类似外键的列（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
    • 2
  3. Best Answer

    强大的SQL Parser 可以帮助自动分析成百上千个ad-hoc 查询和存储过程，并且从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

相关问题