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
) 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
