Recherches générique dans tous le code procédural de SQL Server

Voici une petite procédure permettant de rechercher dans tous le code SQL de toutes les bases d’un serveur, un élément littéral.


USE master;
GO
 
CREATE SCHEMA CODE_GENERIQUE;
GO
 
CREATE PROCEDURE CODE_GENERIQUE.P_SEARCH_CODE_OBJECT @objname NVARCHAR(300)
-- SQLspot Fred Brouard 20110503
AS
/******************************************************************************
* Frédéric Brouard - alias SQLpro - http://sqlpro.developpez.com - 2011-05-15 *
*******************************************************************************
* Recherche d'objet dans tous les fichiers de code SQL                        *
* @objname : non de l'objet cherché                                           *
*******************************************************************************
* copyright : Frédéric Brouard / SQLpro / SQLspot  -  http://www.sqlspot.com  *
* Expertise, audit, tuning, optimisation, conseil, formation... MS SQL Server *
******************************************************************************/
 
--> @objname : exemple : "sp_commande_terminer" ou "dbo.sp_commande_terminer" ou "gescom.dbo.sp_commande_terminer"
BEGIN
DECLARE @CRLF NCHAR(2),
        @SQL VARCHAR(max);
SET @objname = 'sp_commande_terminer'
SELECT @SQL = '', @CRLF = CHAR(13) + CHAR(10);
SELECT @SQL = @SQL  
+ 'SELECT ROUTINE_CATALOG COLLATE SQL_Latin1_General_CP1_CI_AI AS DATABASE_NAME, ROUTINE_SCHEMA +''.'' + ROUTINE_NAME COLLATE SQL_Latin1_General_CP1_CI_AI AS PRGM_NAME, ROUTINE_TYPE COLLATE SQL_Latin1_General_CP1_CI_AI ' + @CRLF
+ 'FROM   ' + name + '.INFORMATION_SCHEMA.ROUTINES' + @CRLF
+ 'WHERE  ROUTINE_DEFINITION LIKE ''%' + @objname + '%'' ' + @CRLF
+ 'UNION  ALL ' + @CRLF
+ 'SELECT ''' + name +''' COLLATE SQL_Latin1_General_CP1_CI_AI AS DATABASE_NAME,  t.name +''.'' + s.name COLLATE SQL_Latin1_General_CP1_CI_AI, ''TRIGGGER'' COLLATE SQL_Latin1_General_CP1_CI_AI ' + @CRLF  
+ 'FROM   sys.triggers AS t ' + @CRLF
+ '       INNER JOIN sys.objects AS o' + @CRLF
+ '             ON t.parent_id = o.object_id' + @CRLF
+ '       INNER JOIN sys.schemas AS s' + @CRLF
+ '             ON o.schema_id = s.schema_id' + @CRLF
+ '       INNER JOIN sys.sql_modules AS q' + @CRLF
+ '             ON t.object_id = q.object_id' + @CRLF
+ 'WHERE  definition LIKE ''%' + @objname + '%''' + @CRLF
+ 'UNION ALL' + @CRLF
FROM   master.sys.databases;
 
SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) - 11);
 
EXEC(@SQL);
 
END
 
GO

–> exemple de recherche :
EXEC CODE_GENERIQUE.P_SEARCH_CODE_OBJECT 'hypothenuse'


--------
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation
* * * * *  Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence  * * * * *

MVP Microsoft SQL Server

Une réflexion au sujet de « Recherches générique dans tous le code procédural de SQL Server »

  1. Avatar de ZabriskirZabriskir

    2 remarques :
    Рla colonne ROUTINE_DEFINITION ne renvoie que les 4000 premiers caract̬res,
    – votre procstock ne scrute pas les vues.

    Je propose une version modifiée :

    DECLARE @string as NVARCHAR(300)
    SET @string = ‘TOTO’

    DECLARE @CRLF NCHAR(2),
    @SQL VARCHAR(max);

    SELECT @SQL =  », @CRLF = CHAR(13) + CHAR(10);
    SELECT @SQL = @SQL
    + ‘SELECT ‘ + @CRLF
    +  » » + name +  »’ AS DATABASE_NAME ‘ + @CRLF
    + ‘,vmod.object_id AS SQL_MODULE_ID ‘ + @CRLF
    + ‘,vsch.name +  ». » + vobj.name COLLATE French_BIN AS SQL_MODULE_NAME ‘ + @CRLF
    + ‘,vobj.type_desc COLLATE French_BIN AS SQL_MODULE_TYPE ‘ + @CRLF
    + ‘FROM ‘ + name + ‘.sys.sql_modules vmod ‘ + @CRLF
    + ‘INNER JOIN ‘ + name + ‘.sys.objects vobj ON vmod.object_id = vobj.object_id ‘ + @CRLF
    + ‘INNER JOIN ‘ + name + ‘.sys.schemas vsch ON vobj.schema_id = vsch.schema_id ‘ + @CRLF
    + ‘WHERE vmod.definition LIKE  »%’ + @string + ‘% » ‘ + @CRLF
    + ‘UNION ALL ‘ + @CRLF
    FROM master.sys.databases;

    SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) – 12);

    EXEC(@SQL)

Laisser un commentaire