How do I find a stored procedure containing <text>?
Sql ServerSql Server-2008TsqlStored ProceduresSql Server Problem Overview
I need to search a SQL server 2008 for stored procedures containing where maybe the name of a database field or variable name.
Sql Server Solutions
Solution 1 - Sql Server
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%'
Solution 2 - Sql Server
Grab yourself a copy of the free Red-Gate SQL Search tool and start enjoying searching in SQL Server! :-)
It's a great and very useful tool, and YES! it's totally, absolutely FREE for any kind of use.
Solution 3 - Sql Server
I took Kashif's answer and union'd all of them together. Strangely, sometimes, I found results in one of the selects but not the other. So to be safe, I run all 3 when I'm looking for something. Hope this helps:
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
Solution 4 - Sql Server
You can also try ApexSQL Search - trial version SSMS plug-in from ApexSQL.
Solution 5 - Sql Server
In case you needed schema as well:
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
Solution 6 - Sql Server
First ensure that you're running the query under your user credentials, and also in the right database context.
USE YOUR_DATABASE_NAME;
Otherwise, sys.procedures won't return anything. Now run the query as below:
select * from sys.procedures p
join sys.syscomments s on p.object_id = s.id
where text like '%YOUR_TEXT%';
Another option is to use INFORMATION_SCHEMA.ROUTINES.ROUTINE_DEFINITION, but be aware that it only holds limited number of characters (i.e., first 4000 characters) of the routine.
select * from YOUR_DATABASE_NAME.INFORMATION_SCHEMA.ROUTINES
where ROUTINE_DEFINITION like '%YOUR_TEXT%';
I tested on Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
Solution 7 - Sql Server
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%FieldName%'
AND ROUTINE_TYPE='PROCEDURE'
Solution 8 - Sql Server
I tried above example but it was not showing more than 4000 characters then I modified it little bit and was able to get whole stored procedure definition. Please see the updated script below -
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
Solution 9 - Sql Server
For any SQL server newer than 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
If someone is stuck with SQL server 2000, the table sql_modules doesn't exist, so you would use syscomments, you will get multiple records for stored procdedures larger than 4000 characters, but they will have the same c.number field so you can group the parts together to get the full stored procedure text:
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
Solution 10 - Sql Server
select * from sys.system_objects
where name like '%cdc%'
Solution 11 - Sql Server
Here is the union version that use Losbear made, but modified so that it also outputs the schema name:
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
Solution 12 - Sql Server
How to Find a Stored Procedure Containing Text or String
Many time we need to find the text or string in the stored procedure. Here is the query to find the containing text.
SELECT OBJECT_NAME(id)
FROM SYSCOMMENTS
WHERE [text] LIKE '%Text%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id)
For more information please check the given URL given below.
http://www.freshcodehub.com/Article/34/how-to-find-a-stored-procedure-containing-text-or-string
Solution 13 - Sql Server
SELECT * FROM sys.procedures WHERE Name LIKE '%CompanySpecialisation%'
This is what I have written in SQL 2012 to select all stored procedures where name like "CompanySpecialisation"
Solution 14 - Sql Server
SELECT name FROM sys.procedures WHERE Object_definition(object_id) LIKE '%FOO%'
Solution 15 - Sql Server
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
Solution 16 - Sql Server
sp_msforeachdb 'use ?;select name,''?'' from sys.procedures where object_definition(object_id) like ''%text%'''
This will search in all stored procedure of all databases. This will also work for long procedures.
Solution 17 - Sql Server
Here is some method of search and find stored procedures in SQL Server by the text or phrase or table name or column name used in the definition
We can use the sys.procedures, INFORMATION_SCHEMA.ROUTINES, sys.syscomments or sys.sql_modules to get the list of stored procedures containing the text or table name or column name.
In all the below methods replace the search condition Employee with your search phrase like table name, column name, view name or any other text in the stored procedure’s definition.
1: Sys.Procedures
You can use the sys.procedures along with OBJECT_DEFINITION built-in meta data function to search within the definition or source code of the stored procedure.
SELECT
name,
OBJECT_DEFINITION(object_id)
FROM
sys.procedures
WHERE
OBJECT_DEFINITION(object_id) LIKE '%Employee%'
2: INFORMATION_SCHEMA.ROUTINES
SELECT
ROUTINE_NAME,
ROUTINE_DEFINITION ,
ROUTINE_SCHEMA
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_TYPE='PROCEDURE' AND
ROUTINE_DEFINITION LIKE '%Employee%'
3: Sys.SysComments
SELECT
OBJECT_NAME(id),
text
FROM
sys.syscomments
WHERE
OBJECTPROPERTY(id, 'IsProcedure') = 1 AND
text LIKE '%Employee%'
ORDER BY OBJECT_NAME(id)
4: Sys.Sql_Modules
SELECT
object_id,
OBJECT_NAME(object_id),
definition
FROM
sys.sql_modules
WHERE
OBJECTPROPERTY(object_id, 'IsProcedure') = 1 AND
definition LIKE '%Employee%'
Solution 18 - Sql Server
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
This query returns the name and the content of any stored procedure where "XXXX" is is referenced within the stored procedure.
This is quit usefull when finding procedures that reference a specific table/view/procedure
Solution 19 - Sql Server
Stored Procedure for find text 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
Solution 20 - Sql Server
SELECT OBJECT_NAME(id)
FROM syscomments
WHERE [text] LIKE '%Name%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id)
Try This .....
Solution 21 - Sql Server
I use this script. If you change your XML Comments to display as black text on a yellow background you get the effect of highlighting the text you're looking for in the xml column of the results. (Tools -> Options -> Environment -> Fonts and Colors [Display items: 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), '')), '&', '(A M P)'), '<', '(L T)'), '>', '(G T)'), @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 -----------------
---------------------------------------------
---------------------------------------------
Solution 22 - Sql Server
Try this request:
Query
SELECT name
FROM sys.procedures
WHERE Object_definition(object_id) LIKE '%xyz%'
Solution 23 - Sql Server
try following code:
DECLARE @SearchText NVARCHAR(2000) = N'YourText';
SELECT DISTINCT SCHEMA_NAME(o.schema_id),o.name,[text] AS MainTextProc
FROM sys.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 c.[text] LIKE '%'+ @SearchText +'%'
ORDER BY SCHEMA_NAME(o.schema_id),o.[name]