Descoberta de chaves estrangeiras pretendidas de JOINS em scripts

Question

Eu estou herdando um banco de dados que tem 400 tabelas e apenas 150 restrições de chave estrangeira registradas. Sabendo o que eu faço sobre o aplicativo e olhando as colunas da tabela, é fácil dizer que deveria haver muito mais.

Eu tenho medo de que o software atual do aplicativo seja quebrado se eu começar a adicionar os FKs ausentes porque os desenvolvedores provavelmente passaram a confiar nessa "liberdade", mas o primeiro passo para corrigir o problema é apresentar a lista de faltando FKs para que possamos avaliá-los como uma equipe.

Para piorar, as colunas de referência não compartilham uma convenção de nomenclatura.

Os relacionamentos são codificados informalmente em centenas de consultas ad-hoc e procedimentos armazenados, portanto, minha esperança é analisar esses arquivos programaticamente procurando JOINS entre as tabelas reais (mas não as variáveis de tabela, etc.).

Os desafios que prevejo nesta abordagem são: novas linhas, aliases opcionais e dicas de tabela, resolução de alias.

• Alguma ideia melhor? (Além de desistir)
• Existem ferramentas pré-criadas que podem resolver isso?
• Eu não acho que o regex possa lidar com isso. Você não concorda?
• Analisadores de SQL? Eu tentei usar o Microsoft.SqlServer.Management.SqlParser.Parser, mas tudo o que está exposto é o lexer - não consigo tirar um AST - tudo isso é interno.

Answer 1

Poderoso analisador de SQL pode ajudar a analisar as centenas de consultas ad-hoc e procedimentos armazenados automaticamente, e da árvore de análise de consulta gerada pelo SQL Parser, você pode facilmente encontrar o relacionamento das variáveis tabela / colunas.

Aqui está uma amostra:

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 

Depois de analisar essa consulta, você pode obter algo assim:

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

Você pode verificar esta demo para informações detalhadas.

    

Answer 2

Eu sinto sua dor.

O SQL Search SSMS gratuito pode ser útil para você.

Em geral, sim, regex pode lidar com isso, mas você deve estar ciente do ponto de diminuir retornos na tentativa de conjurar magia de regex. Você pode ser melhor apenas revisar e pesquisar o código enquanto mapeia os relacionamentos.

A Pesquisa do SQL pode facilitar bastante isso para você.

    

Answer 3

Aqui está o que eu criei. Esta consulta procura por colunas de tipo chave estrangeira (int, bigint, guid) que não são a chave primária da tabela e que não estão atualmente registradas com uma restrição de chave estrangeira. Claro, recebo algumas colunas de Ordem de Classificação e Quantidade, mas realmente reduz a lista com menos esforço do que a análise de scripts 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