T-SQL Script para Encontrar os Nomes de Stored Procedures que usam SQL Dinâmico
Este script foi desenvolvido para responder à pergunta neste tópico do Fórum: Eu preciso de uma consulta para encontrar todas as SPs que utilizam SQL dinâmico.
Podemos executar SQL dinâmico usando sp_executesql ou apenas com EXEC / EXECUTE.
Para encontrar os nomes das Stored Procedures que podem ter usado o SQL dinâmico, este script pode ser usado:
SELECT Schema_name(Schema_id)+'.'+Object_Name(M.Object_id) StoredProceduresWithDynamicSQL
FROM sys.sql_modules M
JOIN sys.objects O ON M.object_id = O.object_id
WHERE definition LIKE '%CREATE PROC%'
AND (definition LIKE '%SP_ExecuteSQL%' OR definition LIKE '%EXEC%')
EXEC / EXECUTE podem ser usados dentro de uma Stored Procedure para chamar outras Stored Procedures ou para executar um SQL dinâmico. Assim, para eliminar os nomes de Stored Procedures que referênciam à outra Stored Procedure e encontrar os nomes de Stored Procedure, que usom EXEC / EXECUTE somente para executar SQL dinâmico, o script à seguir pode ser usado:
SELECT Schema_name(Schema_id)+'.'+Object_Name(M.Object_id) StoredProceduresWithDynamicSQL
FROM sys.sql_modules M
JOIN sys.objects O ON M.object_id = O.object_id
WHERE definition LIKE '%CREATE PROC%'
AND (definition LIKE '%SP_ExecuteSQL%' OR definition LIKE '%EXEC%')
EXCEPT
SELECT StoredProcedure FROM (
SELECT Schema_name(Schema_id)+'.'+Object_Name(M.Object_id) StoredProcedure
FROM sys.sql_modules M
JOIN sys.objects O ON M.object_id = O.object_id
WHERE definition LIKE '%CREATE PROC%'
AND (definition LIKE '%SP_ExecuteSQL%' OR definition LIKE '%EXEC%')) tmp
CROSS APPLY sys.dm_sql_referenced_entities (StoredProcedure, 'OBJECT');
O script acima não funciona nas seguintes situações:
- Se temos usado EXEC / EXECUTE dentro de uma Stored Procedure para ambos os fins, ou seja, para chamar outro procedimento armazenado e para executar um SQL dinâmico e outro cenário;
- Se temos usado sp_executesql ou EXEC / EXECUTE e comentada dentro de uma Stored Procedure, mas ainda assim os scripts acima vai ser útil, pois não temos outra maneira direta para encontrar os nomes de procedimento armazenado que tem usado o SQL dinâmico;
- Este script também não vai funcionar para Stored Procedure criptografadas;
Veja Também
Outros Idiomas
Este artigo também está disponível nos seguintes idiomas: