Vous recherchez un texte dans tous les codes des procédures, fonctions ou vues SQL Server voici une requête qui permet de faire cela...
C'est utile dans le cas suivant ;
On change les paramètres d'une fonction ou procédure. Ou est appelée cette fonction ? Quels sont les impacts de mon changement ?
La procédure utilise largement le schéma système
sys de SQL Server avec les tables (ou vues) :
- sys.all_objects : qui liste les objets qui nous interresse ici
- sys.schemas pour avoir les noms des schémas
- sys.sql_modules pour avoir le contenu des procédures
- sys.system_sql_modules pour avoir le contenu des procédure systèmes s'il y a lieu
La table
sys.all_objects : dispose d'une colonne
type qui indique de quoi on parle :
- 'P' ou 'PC pour une procédure
- 'FN' ou 'TF' ou 'IF' pour une fonction
- 'V' pour une vue
En enlevant ces filtres on s'assure d'avoir tous les objets de la base. Cela peut être utile en fonction des besoins
Le code SQL :
DECLARE @txt
VARCHAR(
MAX) =
'xxx' --- Texte à chercher
SELECT *
FROM (
SELECT sp.object_id AS [id]
, CASE sp.type WHEN 'AF' THEN 'Fonction d''agrégation (CLR)'
WHEN 'C' THEN 'Contrainte CHECK'
WHEN 'D' THEN 'DEFAULT (contrainte ou autonome)'
WHEN 'F' THEN 'Contrainte FOREIGN KEY'
WHEN 'FN' THEN 'Fonction scalaire SQL'
WHEN 'FS' THEN 'Fonction scalaire d''assembly (CLR)'
WHEN 'FT' THEN 'Fonction table d''assembly (CLR)'
WHEN 'IF' THEN 'Fonction table en ligne SQL'
WHEN 'IT' THEN 'Table interne'
WHEN 'P' THEN 'Procédure stockée SQL'
WHEN 'PC' THEN 'Procédure stockée d’assembly (CLR)'
WHEN 'PG' THEN 'Repère de plan'
WHEN 'PK' THEN 'Contrainte PRIMARY KEY'
WHEN 'R ' THEN 'Règle (ancienne, autonome)'
WHEN 'RF' THEN 'Procédure de filtre de réplication'
WHEN 'S ' THEN 'Table de base système'
WHEN 'SN' THEN 'Synonyme'
WHEN 'SO' THEN 'Objet séquence'
WHEN 'SQ' THEN 'File d''attente du service'
WHEN 'TA' THEN 'Déclencheur d''assembly DML (CLR)'
WHEN 'TF' THEN 'Fonction table SQL'
WHEN 'TR' THEN 'Déclencheur DML SQL'
WHEN 'TT' THEN 'Type de table'
WHEN 'U' THEN 'Table (définie par l''utilisateur)'
WHEN 'UQ' THEN 'Contrainte UNIQUE'
WHEN 'V' THEN 'Vue'
WHEN 'X' THEN 'Procédure stockée étendu'
ELSE sp.type END AS [type]
, s.[name] AS [schema], sp.[name] AS [nom]
, ISNULL(smsp.[definition], ssmsp.[definition]) AS [code_sql]
FROM sys.all_objects AS sp
INNER JOIN sys.schemas AS s ON sp.schema_id = s.schema_id
LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
WHERE CAST(CASE WHEN sp.is_ms_shipped = 1 THEN 1
WHEN (SELECT p.major_id
FROM sys.extended_properties AS p
WHERE p.major_id = sp.object_id
AND p.minor_id = 0
AND p.class = 1
AND p.name = N'microsoft_database_tools_support'
) IS NOT NULL THEN 1
ELSE 0 END AS bit) = 0
AND smsp.execute_as_principal_id IS NULL
) pfv
WHERE pfv.code_sql LIKE '%' + @txt + '%'
ORDER BY [type], [schema], [nom]
Enjoy
ADD 07-2018
Manière plus simple d'obtenir le code d'une procédure / fonction / vue / trigger /... (ça ne marche pas pour les tables !)
SELECT object_definition(object_id('[Nom schéma].[Nom Objet]')) AS [Definition] FOR XML PATH('')
La clause
FOR XML évite que SSMS tronque le résultat !
Plus simple
A adapter dans ce qui précède si besoin d'élargir les recherches
EDIT 08/2021
Mise à jour de la procédure pour remonter tous les cas possible de code.
