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