Откройте для себя предполагаемые иностранные ключи от JOINS в скриптах

Я наследую базу данных с 400 таблицами и только 150 зарегистрированными ограничениями внешнего ключа. Зная, что я делаю с приложением и просматривая столбцы таблицы, легко сказать, что должно быть намного больше.

Я боюсь, что текущее прикладное программное обеспечение сломается, если я начну добавлять недостающие FK, потому что разработчики, вероятно, стали полагаться на эту "свободу", но первый шаг в исправлении проблемы - составить список отсутствующих FK, так что мы можем оценить их как команду.

Что еще хуже, ссылочные столбцы не имеют общего соглашения об именах.

Отношения неофициально закодированы в сотнях специальных запросов и хранимых процедур, поэтому я надеюсь, что эти файлы будут проанализированы программным путем в поисках JOINS между реальными таблицами (но не переменными таблиц и т. Д.).

Проблемы, которые я предвижу в этом подходе: новые строки, дополнительные псевдонимы и табличные подсказки, разрешение псевдонимов.

  • Есть идеи получше? (Кроме выхода)
  • Есть какие-то готовые инструменты, которые могут решить эту проблему?
  • Я не думаю, что регулярные выражения могут справиться с этим. Вы не согласны?
  • Парсеры SQL? Я пытался использовать Microsoft.SqlServer.Management.SqlParser.Parser, но все, что раскрывается, это лексер - не может получить AST из него - все эти вещи являются внутренними.
Источник

3 ответа

Решение

Мощный анализатор SQL может помочь автоматически проанализировать сотни специальных запросов и хранимых процедур, а из дерева синтаксического анализа запросов, созданного анализатором SQL, можно легко найти связь между таблицей / столбцами переменных.

Вот образец:

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

Вы можете проверить это демо для подробной информации.

Источник

Я чувствую твою боль.

Бесплатная надстройка SQL Search SSMS может быть полезна для вас.

В общем, да, регулярные выражения могут справиться с этим, но вы должны знать о точке с уменьшением отдачи при попытке вызвать магию регулярных выражений. Возможно, вам будет лучше просто просмотреть и выполнить поиск по коду во время составления карты отношений.

SQL Search может сделать это намного проще для вас.

Источник

Вот что я придумал. Этот запрос ищет столбцы, подобные внешнему ключу (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
Источник
Другие вопросы по тегам