Recherche dans une stored proc

Des fois, nous avons besoin de retrouver une ou des stored procs dans laquelle nous avons laissé un commentaire quelconque (comme par exemple, les “-- TODO: “) ou bien retrouver quelles stored procs utilise une table en particulier lorsqu'on utilise du SQL dynamique.

Il existe plusieurs façon d'obtenir la définition d'une stored proc, soit en utilisant la fonction OBJECT_DEFINTION (OBJECT_ID), soit en utilisant la vue INFORMATION_SCHEMA.ROUTINES ou en utilisant la vue système sys.sql_modules.

Dans ce cas-ci, on allons utiliser la vue système sys.sql_modules pour obtenir la définition des stored proc dans la base de données au lieu de INFORMATION_SCHEMA.ROUTINES étant donnée que cette dernière ne retourne que les 4000 premiers caractères.

En plus de la définition des stored procs de la base de données, la vue sys.sql_modules nous donne accès aux caractérisques des stored procs (créée ou non avec SCHEMABINDING, WITH RECOMPILE, ...) à l'exception du nom de la stored proc et de son schema, ce qui peut causer des désagrément.

Maintenant qu'on a ces informations, on veut restreindre le nombre d'occurrence retourné pour avoir que ceux qui contiennent le texte recherché. Dans notre exemple, on recherchera la ou les storeds proc qui impriment les erreurs (« print error »).

Nous avons donc trouvé la stored proc qui contient « print error ». Il nous reste à connaître son nom ainsi que son schema. Pour cela, on utilise les fonctions OBJECT_SCHEMA_NAME et OBJECT_NAME pour obtenir les informations qui nous manque.

Et voilà, nous avons donc trouvé le bon nom d'objet dont la définition contenait les mots « print error ».

Ceci dit, lorsqu'on cherche dans l'ensemble des définitions de la vue sql_modules, on voudra parfois restreindre la portée de notre recherche. La vue sys.sql_modules contient l'ensemble des définitions pour les objets de type P (SQL Stored Procedure), RF (Replication-filter-procedure), V (View), TR (SQL DML Trigger), FN (SQL scalar function), IF (SQL inline table-valued function), TF (SQL table-valued-function) et R (rule, old-style, stand-alone).

Pour limiter la portée et n'inclure que les définitions qui correspondent à un type d'objet en particulier, on joindra la vue sys.objects dans notre requête. En joignant ces deux vues, on avons donc accès au type de l'objet, mais aussi à d'autres informations qui peuvent nous aider à filtrer selon nos besoins, comme par exemple, si on voudrait obtenir les stored procs qui ont été modifiées depuis leur création.

Encore une fois, il nous manque des informations pour avoir le nom complet. On utilisera la fonction SCHEMA_NAME avec le champ schema_id pour obtenir le nom complet de l'objet.

Référence : https://msdn.microsoft.com/en-us/library/ms175081.aspx (sys.sql_modules)

                  https://msdn.microsoft.com/en-us/library/ms190324.aspx (sys.objects)