Como faço para encontrar um procedimento armazenado contendo ?

Eu preciso procurar um servidor SQL 2008 para stored procedures contendo onde talvez o nome de um campo de database ou nome da variável.

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%' 

Pegue uma cópia da ferramenta gratuita Red-Gate SQL Search e comece a curtir a pesquisa no SQL Server! 🙂

insira a descrição da imagem aqui

É uma ótima ferramenta e muito útil, e sim! É totalmente GRÁTIS para qualquer tipo de uso.

Você também pode experimentar o plug-in SSMS do ApexSQL, que é uma pesquisa do ApexSQL.

insira a descrição da imagem aqui

Eu peguei a resposta de Kashif e juntei todos eles juntos. Estranhamente, às vezes, encontrei resultados em um dos seletos, mas não no outro. Então, para ser seguro, eu corro todos os 3 quando estou procurando por algo. Espero que isto ajude:

 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' 

Caso você também precise de esquema:

 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 

Eu tentei acima exemplo, mas não foi mostrando mais de 4000 caracteres, então eu modifiquei um pouco e foi capaz de obter toda a definição do procedimento armazenado. Por favor, veja o script atualizado abaixo –

 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 

Primeiro, verifique se você está executando a consulta com suas credenciais de usuário e também no contexto de database correto.

 USE YOUR_DATABASE_NAME; 

Caso contrário, sys.procedures não retornará nada. Agora execute a consulta conforme abaixo:

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

Outra opção é usar INFORMATION_SCHEMA.ROUTINES.ROUTINE_DEFINITION, mas esteja ciente de que ele contém apenas um número limitado de caracteres (ou seja, os primeiros 4000 caracteres) da rotina.

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

Eu testei no 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 

Como localizar um procedimento armazenado contendo texto ou seqüência de caracteres

Muitas vezes precisamos encontrar o texto ou string no procedimento armazenado. Aqui está a consulta para encontrar o texto que contém.

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

Para mais informações, por favor, verifique o URL fornecido abaixo.

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

Para qualquer servidor SQL mais recente que o 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 alguém está preso com o SQL server 2000, a tabela sql_modules não existe, então você usaria syscomments, você obterá vários registros para procdedures armazenados maiores que 4000 caracteres, mas eles terão o mesmo campo c.number para que você possa agrupar as partes juntas para obter o texto completo do procedimento armazenado:

  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 

Você também pode tentar este programa utilitário que eu escrevi, é mais eficiente usar uma ferramenta rápida do que escrever uma consulta SQL. Ele também irá procurar seus arquivos de código-fonte.

 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 

Essa consulta retorna o nome e o conteúdo de qualquer procedimento armazenado em que “XXXX” é referenciado no procedimento armazenado.

Isso é útil ao localizar procedimentos que fazem referência a uma tabela / exibição / procedimento específico

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

Isso é o que escrevi no SQL 2012 para selecionar todos os stored procedures em que nomes como “CompanySpecialisation”

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

Procedimento armazenado para localizar texto no 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) 

Experimente isso …..

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

Isso pesquisará em todos os stored procedures de todos os bancos de dados. Isso também funcionará para procedimentos longos.

Eu uso esse script. Se você alterar seus comentários XML para exibir como texto preto em um fundo amarelo, você terá o efeito de destacar o texto que está procurando na coluna xml dos resultados. (Ferramentas -> Opções -> Ambiente -> Fontes e Cores [Exibir itens: XML Comment]

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

Aqui está a versão da união que usa o Losbear, mas modificada para que também gere o nome do esquema:

 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