Search Text in Stored Procedure in SQL Server

Search text in stored procedure in SQL Server

Escape the square brackets:

...
WHERE m.definition Like '%\[ABD\]%' ESCAPE '\'

Then the square brackets will be treated as a string literals not as wild cards.

How do I find a stored procedure containing text ?

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

Find text in my tables, stored procedures, and views in SQL Server

Please try this query for column search

SELECT 
    t.[name]    TableName
    , c.[name]  ColumnName
FROM sys.columns c
INNER JOIN sys.tables t on t.object_id = c.object_id
WHERE t.[type] = 'U' 
AND c.[name] LIKE '%Text%'

Below script will get result for all the databases for both Tables and other obejcts. Hope this will help.

DECLARE @command varchar(1000) 
DECLARE @SearchWord VARCHAR(20) = 'Text'
CREATE TABLE #Search (DatabaseName VARCHAR(255),SchemaName VARCHAR(50),ObjectName VARCHAR(255),ObjectType VARCHAR(50))

SET @command = 'USE ? INSERT INTO #Search
                    SELECT DB_NAME(), SCHEMA_NAME(t.schema_id),t.[name] TableName, ''Table'' FROM sys.columns c INNER JOIN sys.tables t on t.object_id = c.object_id WHERE t.[type] = ''U'' AND c.[name] LIKE ' + '''%' + @SearchWord + '%'''
EXEC sp_MSforeachdb @command;

SET @command = 'USE ? INSERT INTO #Search
                SELECT DISTINCT DB_Name(),s.name AS Schema_Name, o.name AS Object_Name, o.type_desc FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id = o.object_id INNER JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE m.definition Like ''%' + @SearchWord + '%'''
EXEC sp_MSforeachdb @command 
SELECT * FROM #Search;
DROP TABLE #Search

Find a string by searching all stored procs in SQL Server Management Studio 2008

SELECT *
FROM sys.sql_modules
WHERE definition LIKE '%yourstring%'

Find all stored procedures including a text in their body

this will find all procedures, functions, triggers, etc with the text Xin it

SELECT DISTINCT
       o.name AS Object_Name,
       o.type_desc,
       m.*
FROM   sys.sql_modules m
  INNER JOIN sys.objects o ON m.object_id = o.object_id
WHERE m.definition Like '%X%'

It is easy to alter so you only get procedures, or whatever you need

Search values in string while looping - stored procedure

Try this:

; WITH kvRows AS (
    SELECT  a.Category, 
            a.Step, 
            a.Value1, 
            b.value_field, 
            pos = PATINDEX('%'+a.Value1+'%', b.value_field)
    FROM #table_a a
    INNER JOIN #table_b b
    ON PATINDEX('%'+a.Value1+'%', b.value_field) > 0
) 
SELECT  x.Category, 
        r.value_field, 
        x.Step1, 
        x.Step2, 
        x.Step3, 
        x.Step4 
FROM kvRows r
CROSS APPLY (VALUES
    --- pivot rows to columns using "VALUES"
    (Category, IIF(Step=1, pos, 0), IIF(Step=2, pos, 0), IIF(Step=3, pos, 0), IIF(Step=4,pos,0)) 
) as x(Category, Step1, Step2, Step3, Step4) 

How to find a text inside stored procedures across multiple databases

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

SELECT @sql = @sql + 'SELECT db = ''' + name + ''', o.name, o.type_desc
  FROM ' + QUOTENAME(name) + '.sys.sql_modules AS m
  INNER JOIN ' + QUOTENAME(name) + '.sys.objects AS o
  ON m.[object_id] = o.[object_id]
  WHERE m.definition LIKE N''%'' + @Search + ''%'' 
  ORDER BY o.type_desc, o.name;'
FROM sys.databases
WHERE database_id > 4 AND state = 0; -- online

EXEC sp_executesql @sql, N'@Search NVARCHAR(255)', N'sp_reset_data';

Strictly speaking, if you only want procedures, then it is a little simpler (the above will also include functions, triggers, even views):

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

SELECT @sql = @sql + 'SELECT db = ''' + name + ''', o.name
  FROM ' + QUOTENAME(name) + '.sys.sql_modules AS m
  INNER JOIN ' + QUOTENAME(name) + '.sys.procedures AS o
  ON m.[object_id] = o.[object_id]
  WHERE m.definition LIKE N''%'' + @Search + ''%'' 
  ORDER BY o.name;'
FROM sys.databases
WHERE database_id > 4 AND state = 0; -- online

EXEC sp_executesql @sql, N'@Search NVARCHAR(255)', N'sp_reset_data';

---

Related Topics