SQL Server: Listar JOBs que usam uma determinada tabela - STRING_AGG 🔎

📋 Descrição:

Este artigo explica como listar as procedures que utilizam tabelas específicas dentro de um banco de dados SQL Server. A consulta elimina repetições, utilizando a função STRING_AGG para apresentar os resultados de forma compacta em uma única coluna. A função STRING_AGG foi introduzida no SQL Server 2017 e permite concatenar valores em uma única string, o que é ideal para esse tipo de consulta, onde várias procedures podem fazer referência a uma ou mais tabelas.

🛠️ Estrutura da Query:

1️⃣ Criação de Tabela Temporária para Armazenar as Referências:

Primeiramente, a consulta cria uma tabela temporária #table_proc para armazenar as tabelas e as procedures que fazem referência a essas tabelas. Aqui, o relacionamento entre as tabelas e as procedures é feito através das tabelas do sistema sys.objects e sys.sql_expression_dependencies.

SELECT DISTINCT o.Name AS table_name, sp.Name AS proc_name INTO #table_proc
FROM
    sys.objects o
      INNER JOIN sys.sql_expression_dependencies sd
          ON o.object_id = sd.referenced_id
      INNER JOIN sys.objects sp
          ON sd.referencing_id = sp.object_id
              AND sp.type IN ('P') -- Filtrando apenas procedures (P)
WHERE
      o.name IN ('tabela1', 'tabela2', '...', 'tabela(n)') -- Substitua pelos nomes das tabelas desejadas
ORDER BY
      o.Name, sp.Name;

• sys.objects: Armazena informações sobre todos os objetos no banco de dados.
• sys.sql_expression_dependencies: Contém informações sobre dependências entre objetos no banco de dados, como tabelas e procedures.

Essa parte da consulta identifica as tabelas e procedures associadas, armazenando os resultados na tabela temporária #table_proc>.

2️⃣ Uso da Função STRING_AGG para Concatenar os Resultados:

A segunda parte da consulta utiliza a função STRING_AGG para agrupar todas as procedures que utilizam uma tabela específica em uma única coluna chamada all_procs. Isso elimina a necessidade de exibir uma linha para cada referência, condensando tudo em uma única linha por tabela.

SELECT
      table_name,
      STRING_AGG(CONVERT(NVARCHAR(max),
      ISNULL(proc_name, 'N/A')), ',') AS all_procs
FROM
      #table_proc
GROUP BY
      table_name;

• STRING_AGG(): Concatena os valores das procedures relacionadas a uma tabela em uma única string, separada por vírgulas.
• ISNULL(proc_name, 'N/A'): Caso uma procedure não tenha nome (se proc_name for NULL), ela é substituída por 'N/A'.
• GROUP BY table_name: Agrupa as informações por nome da tabela, assim as procedures associadas a cada tabela serão listadas juntas.

💡 Exemplo de Utilização:

Imagine que você queira verificar todas as procedures que fazem referência a duas tabelas chamadas Clientes e Pedidos. O código seria:

SELECT DISTINCT o.Name AS table_name, sp.Name AS proc_name 
INTO #table_proc 
FROM 
    sys.objects o 
        INNER JOIN sys.sql_expression_dependencies sd 
            ON o.object_id = sd.referenced_id 
        INNER JOIN sys.objects sp 
            ON sd.referencing_id = sp.object_id AND sp.type IN ('P') -- Filtrando apenas procedures (P) 
WHERE 
    o.name IN ('Clientes', 'Pedidos') -- Tabelas desejadas 
ORDER BY 
    o.Name, sp.Name; 

SELECT 
   table_name, 
   STRING_AGG(CONVERT(NVARCHAR(max), 
   ISNULL(proc_name, 'N/A')), ',') AS all_procs 
FROM 
   #table_proc 
GROUP BY 
   table_name; 

Saída Esperada:

🔧 Considerações Importantes:

• Compatibilidade: A função STRING_AGG está disponível apenas no SQL Server 2017 ou superior. Para versões anteriores, você pode usar FOR XML PATH para obter resultados semelhantes.

• Desempenho: Embora essa consulta seja bastante útil para análise e auditoria, em grandes bancos de dados com muitas dependências, o uso de funções agregadas como STRING_AGG pode impactar o desempenho. Verifique o impacto em ambientes de produção.

• Alterações em Dependências: O script analisa as dependências de objetos no banco de dados. Certifique-se de que a consulta esteja atualizada com os objetos corretos, especialmente quando há alterações no banco.

🔍 Conclusão:

Com o uso da função STRING_AGG, podemos facilmente listar todas as procedures que utilizam determinadas tabelas, agrupando os resultados em uma única linha por tabela. Isso facilita a visualização das dependências entre objetos, essencial para auditoria, manutenção e análise de impacto em um banco de dados.