Come trovo una procedura memorizzata contenente ?

Ho bisogno di cercare un server SQL 2008 per le stored procedure contenenti dove forse il nome di un campo del database o il nome della variabile.

SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%Foo%' AND ROUTINE_TYPE='PROCEDURE' 

 SELECT OBJECT_NAME(id) FROM SYSCOMMENTS WHERE [text] LIKE '%Foo%' AND OBJECTPROPERTY(id, 'IsProcedure') = 1 GROUP BY OBJECT_NAME(id) 

 SELECT OBJECT_NAME(object_id) FROM sys.sql_modules WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1 AND definition LIKE '%Foo%' 

Prendi una copia dello strumento di ricerca SQL Red-Gate gratuito e inizia a divertirti a cercare in SQL Server! 🙂

inserisci la descrizione dell'immagine qui

È uno strumento fantastico e molto utile, e SÌ! è totalmente, assolutamente GRATUITO per qualsiasi tipo di utilizzo.

Puoi anche provare ApexSQL Search – plug-in SSMS gratuito da ApexSQL.

inserisci la descrizione dell'immagine qui

Presi la risposta di Kashif e l’unione di tutti loro insieme. Stranamente, a volte, ho trovato risultati in uno dei selettivi ma non nell’altro. Quindi, per essere al sicuro, corro tutti e 3 quando sto cercando qualcosa. Spero che questo ti aiuti:

 DECLARE @SearchText varchar(1000) = 'mytext'; SELECT DISTINCT SPName FROM ( (SELECT ROUTINE_NAME SPName FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%' + @SearchText + '%' AND ROUTINE_TYPE='PROCEDURE') UNION ALL (SELECT OBJECT_NAME(id) SPName FROM SYSCOMMENTS WHERE [text] LIKE '%' + @SearchText + '%' AND OBJECTPROPERTY(id, 'IsProcedure') = 1 GROUP BY OBJECT_NAME(id)) UNION ALL (SELECT OBJECT_NAME(object_id) SPName FROM sys.sql_modules WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1 AND definition LIKE '%' + @SearchText + '%') ) AS T ORDER BY T.SPName 
 SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%FieldName%' AND ROUTINE_TYPE='PROCEDURE' 

Nel caso in cui avessi bisogno anche dello schema:

 SELECT DISTINCT SCHEMA_NAME(o.schema_id),o.name,[text] FROM syscomments AS c INNER JOIN sys.objects AS o ON c.id = o.[object_id] INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id WHERE text LIKE '%foo%' ORDER BY SCHEMA_NAME(o.schema_id),o.name 

Ho provato sopra l’esempio ma non mostrava più di 4000 caratteri, quindi l’ho modificato un po ‘e sono riuscito a ottenere l’intera definizione di stored procedure. Si prega di consultare lo script aggiornato di seguito –

 SELECT SCHEMA_NAME(O.SCHEMA_ID) [SCHEMA_NAME], O.NAME, OBJECT_DEFINITION(OBJECT_ID) TEXT FROM SYSCOMMENTS AS C INNER JOIN SYS.OBJECTS AS O ON C.ID = O.[OBJECT_ID] INNER JOIN SYS.SCHEMAS AS S ON O.SCHEMA_ID = S.SCHEMA_ID WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%FOO%' ORDER BY SCHEMA_NAME(O.SCHEMA_ID), O.NAME 

Innanzitutto assicurati di eseguire la query sotto le credenziali dell’utente e anche nel contesto del database corretto.

 USE YOUR_DATABASE_NAME; 

Altrimenti, sys.procedures non restituirà nulla. Ora esegui la query come di seguito:

 select * from sys.procedures p join sys.syscomments s on p.object_id = s.id where text like '%YOUR_TEXT%'; 

Un’altra opzione è l’uso di INFORMATION_SCHEMA.ROUTINES.ROUTINE_DEFINITION, ma si tenga presente che contiene solo un numero limitato di caratteri (ovvero, i primi 4000 caratteri) della routine.

 select * from YOUR_DATABASE_NAME.INFORMATION_SCHEMA.ROUTINES where ROUTINE_DEFINITION like '%YOUR_TEXT%'; 

Ho provato su Microsoft SQL Server 2008 R2 (SP1) – 10.50.2500.0 (X64)

 select * from sys.system_objects where name like '%cdc%' 
 create Procedure [dbo].[TextFinder] (@Text varchar(500),@Type varchar(2)=NULL) AS BEGIN SELECT DISTINCT o.name AS ObjectName, CASE o.xtype WHEN 'C' THEN 'CHECK constraint' WHEN 'D' THEN 'Default or DEFAULT constraint' WHEN 'F' THEN 'FOREIGN KEY constraint' WHEN 'FN' THEN 'Scalar function' WHEN 'IF' THEN 'In-lined table-function' WHEN 'K' THEN 'PRIMARY KEY or UNIQUE constraint' WHEN 'L' THEN 'Log' WHEN 'P' THEN 'Stored procedure' WHEN 'R' THEN 'Rule' WHEN 'RF' THEN 'Replication filter stored procedure' WHEN 'S' THEN 'System table' WHEN 'TF' THEN 'Table function' WHEN 'TR' THEN 'Trigger'`enter code here` WHEN 'U' THEN 'User table' WHEN 'V' THEN 'View' WHEN 'X' THEN 'Extended stored procedure' ELSE o.xtype END AS ObjectType, ISNULL( p.Name, '[db]') AS Location FROM syscomments c INNER JOIN sysobjects o ON c.id=o.id LEFT JOIN sysobjects p ON o.Parent_obj=p.id WHERE c.text LIKE '%' + @Text + '%' and o.xtype = case when @Type IS NULL then o.xtype else @Type end ORDER BY Location, ObjectName END 

Come trovare una stored procedure contenente testo o stringa

Molte volte abbiamo bisogno di trovare il testo o la stringa nella stored procedure. Ecco la query per trovare il testo che contiene.

 SELECT OBJECT_NAME(id) FROM SYSCOMMENTS WHERE [text] LIKE '%Text%' AND OBJECTPROPERTY(id, 'IsProcedure') = 1 GROUP BY OBJECT_NAME(id) 

Per ulteriori informazioni si prega di controllare l’URL dato di seguito.

http://www.freshcodehub.com/Article/34/how-to-find-a-stored-procedure-containing-text-or-string

Per qualsiasi server SQL più recente di SQL Server 2000:

 SELECT object_name = OBJECT_NAME(sm.object_id), o.type_desc, sm.definition FROM sys.sql_modules AS sm JOIN sys.objects AS o ON sm.object_id = o.object_id WHERE sm.definition like '%searchString%' ORDER BY o.type, o.name, o.object_id 

Se qualcuno è bloccato con SQL Server 2000, la tabella sql_modules non esiste, quindi useresti syscomments, riceverai più record per stored procedure con più di 4000 caratteri, ma avranno lo stesso campo c.number in modo che tu possa raggruppare le parti insieme per ottenere il testo completo della stored procedure:

  Select o.id, c.number, o.name, c.text from syscomments c inner join sysobjects o on o.id = c.id where c.encrypted = 0 and o.type = 'P' and c.id in (Select id from syscomments where text like '%searchtext%') order by objecttype, o.name, o.id, c.number, c.colid 

Puoi anche provare questo programma di utilità che ho scritto, è più efficiente usare uno strumento veloce che scrivere una query SQL. Cerca anche i file del codice sorgente.

 SELECT s.name + '.' + o.name ProcedureName , c.text ProcedureSteps FROM sys.syscomments c INNER JOIN sys.objects o ON c.id = o.object_id INNER JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE o.type = 'P' AND c.text LIKE N'%XXXX%' ORDER BY s.name + '.' + o.name , c.colid 

Questa query restituisce il nome e il contenuto di qualsiasi stored procedure in cui “XXXX” è referenziato all’interno della stored procedure.

Questo è utile quando si trovano procedure che fanno riferimento a una tabella / vista / procedura specifica

 SELECT * FROM sys.procedures WHERE Name LIKE '%CompanySpecialisation%' 

Questo è quello che ho scritto in SQL 2012 per selezionare tutte le stored procedure in cui nome come “CompanySpecialisation”

 SELECT name FROM sys.procedures WHERE Object_definition(object_id) LIKE '%FOO%' 

Stored procedure per trovare testo in SP .. {Dinesh Baskaran} Trendy Global Systems pvt ltd

  create Procedure [dbo].[TextFinder] (@Text varchar(500),@Type varchar(2)=NULL) AS BEGIN SELECT DISTINCT o.name AS ObjectName, CASE o.xtype WHEN 'C' THEN 'CHECK constraint ' WHEN 'D' THEN 'Default or DEFAULT constraint' WHEN 'F' THEN 'FOREIGN KEY constraint' WHEN 'FN' THEN 'Scalar function' WHEN 'IF' THEN 'In-lined table-function' WHEN 'K' THEN 'PRIMARY KEY or UNIQUE constraint' WHEN 'L' THEN 'Log' WHEN 'P' THEN 'Stored procedure' WHEN 'R' THEN 'Rule' WHEN 'RF' THEN 'Replication filter stored procedure' WHEN 'S' THEN 'System table' WHEN 'TF' THEN 'Table function' WHEN 'TR' THEN 'Trigger' WHEN 'U' THEN 'User table' WHEN 'V' THEN 'View' WHEN 'X' THEN 'Extended stored procedure' ELSE o.xtype END AS ObjectType, ISNULL( p.Name, '[db]') AS Location FROM syscomments c INNER JOIN sysobjects o ON c.id=o.id LEFT JOIN sysobjects p ON o.Parent_obj=p.id WHERE c.text LIKE '%' + @Text + '%' and o.xtype = case when @Type IS NULL then o.xtype else @Type end ORDER BY Location, ObjectName END 
 SELECT OBJECT_NAME(id) FROM syscomments WHERE [text] LIKE '%Name%' AND OBJECTPROPERTY(id, 'IsProcedure') = 1 GROUP BY OBJECT_NAME(id) 

Prova questo …..

 sp_msforeachdb 'use ?;select name,''?'' from sys.procedures where object_definition(object_id) like ''%text%''' 

Questo cercherà in tutte le stored procedure di tutti i database. Questo funzionerà anche per procedure lunghe.

Io uso questo script. Se si modificano i commenti XML da visualizzare come testo nero su uno sfondo giallo, si ottiene l’effetto di evidenziare il testo che si sta cercando nella colonna xml dei risultati. (Strumenti -> Opzioni -> Ambiente -> Caratteri e colors [Visualizza elementi: Commento XML]

  --------------------------------------------- -------------- Start FINDTEXT ---------- --------------------------------------------- SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SET NOCOUNT ON GO DECLARE @SearchString VARCHAR(MAX) SET @SearchString = 'the text you''re looking for' DECLARE @OverrideSearchStringWith VARCHAR(MAX) --############################################################################# -- Use Escape chars in Brackets [] like [%] to find percent char. --############################################################################# DECLARE @ReturnLen INT SET @ReturnLen = 50; with lastrun as (select DEPS.OBJECT_ID ,MAX(last_execution_time) as LastRun from sys.dm_exec_procedure_stats DEPS group by deps.object_id ) SELECT OL.Type ,OBJECT_NAME(OL.Obj_ID) AS 'Name' ,LTRIM(RTRIM(REPLACE(SUBSTRING(REPLACE(OBJECT_DEFINITION(OL.Obj_ID), NCHAR(0x001F), ''), CHARINDEX(@SearchString, OBJECT_DEFINITION(OL.Obj_ID)) - @ReturnLen, @ReturnLen * 2), @SearchString, ' ***-->>' + @SearchString + '<<--*** '))) AS SourceLine ,CAST(REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(MAX), REPLACE(OBJECT_DEFINITION(OL.Obj_ID), NCHAR(0x001F), '')), '&', '(AMP)'), '<', '(LT)'), '>', '(GT)'), @SearchString, '' + @SearchString + '<-->') AS XML) AS 'Hilight Search' ,(SELECT [processing-instruction(A)] = REPLACE(OBJECT_DEFINITION(OL.Obj_ID), NCHAR(0x001F), '') FOR XML PATH('') ,TYPE ) AS 'code' ,Modded AS Modified ,LastRun as LastRun FROM (SELECT CASE P.type WHEN 'P' THEN 'Proc' WHEN 'V' THEN 'View' WHEN 'TR' THEN 'Trig' ELSE 'Func' END AS 'Type' ,P.OBJECT_ID AS OBJ_id ,P.modify_Date AS modded ,LastRun.LastRun FROM sys.Objects P WITH (NOLOCK) LEFT join lastrun on P.object_id = lastrun.object_id WHERE OBJECT_DEFINITION(p.OBJECT_ID) LIKE '%' + @SearchString + '%' AND type IN ('P', 'V', 'TR', 'FN', 'IF', 'TF') -- AND lastrun.LastRun IS NOT null ) OL OPTION (FAST 10) --------------------------------------------- ---------------- END ----------------- --------------------------------------------- --------------------------------------------- 

Ecco la versione di unione che utilizza Losbear, ma modificata in modo che emetta anche il nome dello schema:

 DECLARE @SearchText varchar(1000) = 'searchtext'; SELECT DISTINCT sc.SPECIFIC_SCHEMA as 'Schema', sp.SPName as 'Name' FROM ( (SELECT ROUTINE_NAME SPName FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%' + @SearchText + '%' AND ROUTINE_TYPE='PROCEDURE') UNION ALL (SELECT OBJECT_NAME(id) SPName FROM SYSCOMMENTS WHERE [text] LIKE '%' + @SearchText + '%' AND OBJECTPROPERTY(id, 'IsProcedure') = 1 GROUP BY OBJECT_NAME(id)) UNION ALL (SELECT OBJECT_NAME(object_id) SPName FROM sys.sql_modules WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1 AND definition LIKE '%' + @SearchText + '%') ) AS sp CROSS APPLY (select SPECIFIC_SCHEMA from INFORMATION_SCHEMA.ROUTINES r where r.ROUTINE_NAME = sp.SPName) sc ORDER BY 1