I want to search a text from all my database stored procedures. I use the below SQL:
SELECT DISTINCT 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 WHERE m.definition Like “%
Escape the square brackets:
…WHERE m.definition Like “%
Try this request:
SELECT nameFROM sys.proceduresWHERE Object_definition(object_id) LIKE “%strHell%”
Have you tried using some of the third party tools to do the search? There are several available out there that are free and that saved me a ton of time in the past.
Below are two SSMS Addins I used with good success.
ApexSQL Search – Searches both schema and data in databases and has additional features such as dependency tracking and more…
SSMS Tools pack – Has same search functionality as previous one and several other cool features. Not free for SQL Server 2012 but still very affordable.
I know this answer is not 100% related to the questions (which was more specific) but hopefully others will find this useful.
I usually run the following to achieve that:
select distinct object_name(id) from syscomments where text like “%
Good practice to work with SQL Server.
Create below stored procedure and set short key,
strFind AS VARCHAR(MAX))ASBEGIN SET NOCOUNT ON; –TO FIND STRING IN ALL PROCEDURES BEGIN SELECT OBJECT_NAME(OBJECT_ID) SP_Name ,OBJECT_DEFINITION(OBJECT_ID) SP_Definition FROM sys.procedures WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE “%”+
strFind+”%” END –TO FIND STRING IN ALL VIEWS BEGIN SELECT OBJECT_NAME(OBJECT_ID) View_Name ,OBJECT_DEFINITION(OBJECT_ID) View_Definition FROM sys.views WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE “%”+
strFind+”%” END –TO FIND STRING IN ALL FUNCTION BEGIN SELECT ROUTINE_NAME Function_Name ,ROUTINE_DEFINITION Function_definition FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE “%”+
strFind+”%” AND ROUTINE_TYPE = “FUNCTION” ORDER BY ROUTINE_NAME END –TO FIND STRING IN ALL TABLES OF DATABASE. BEGIN SELECT t.name AS Table_Name ,c.name AS COLUMN_NAME FROM sys.tables AS t INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID WHERE c.name LIKE “%”+
strFind+”%” ORDER BY Table_Name ENDENDNow – Set short key as below,
So next time whenever you want to find a particular text in any of the four objects like Store procedure, Views, Functions and Tables. You just need to write that keyword and press shortcut key.
For example: I want to search “PaymentTable” then write “PaymentTable” and make sure you select or highlight the written keyword in query editor and press shortcut key ctrl+4 – it will provide you full result.
Please take this as a “dirty” alternative but this saved my behind many times especially when I was not familiar with the DB project. Sometimes you are trying to search for a string within all SPs and forget that some of the related logic may have been hiding between Functions and Triggers or it can be simply worded differently than you thought.
From your MSSMS you may right click your DB and selectTasks -> Generate Scripts wizard to output all the SPs, Fns and Triggers into a single .sql file.
Make sure to select Triggers too!
Then just use Sublime or Notepad to search for the string you need to find. I know this may be quite inefficient and paranoid approach but it works 🙂
You can also use this one:
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION like “%Search_String%”
It might help you!
SELECT DISTINCT A.NAME AS OBJECT_NAME, A.TYPE_DESC FROM SYS.SQL_MODULES M INNER JOIN SYS.OBJECTS A ON M.OBJECT_ID = A.OBJECT_ID WHERE M.DEFINITION LIKE “%<"+
SELECT DISTINCT o.name AS Object_Name, o.type_descFROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id = o.object_id WHERE m.definition Like “%
Also you can use:
SELECT OBJECT_NAME(id) FROM syscomments WHERE
SELECT DISTINCT OBJECT_NAME(
id (int) = Object identification number
xtype char(2) Object type. Can be one of the following object types:
FN = Scalar function
P = Stored procedure
V = View
TF = Table function
I created a procedure to search text in procedures/functions, tables, views, or jobs. The first parameter
db is to specify the database to search, default to your current database. Here is my query in dynamic SQL.
target LIKE “”%Procedure%”” BEGIN SELECT o.name As “”Stored Procedures”” FROM SYSOBJECTS o INNER JOIN SYSCOMMENTS c ON o.id = c.id WHERE c.text LIKE
target LIKE “”%View%”” BEGIN SELECT o.name As “”Views”” FROM SYSOBJECTS o INNER JOIN SYSCOMMENTS c ON o.id = c.id WHERE c.text LIKE
search AND o.xtype = “”V”” GROUP BY o.name ORDER BY o.nameEND/* Table – search table name only, need to add column name */ELSE IF
target LIKE “”%Table%”” BEGIN SELECT t.name AS “”TableName”” FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id WHERE c.name LIKE
target LIKE “”%Job%”” BEGIN SELECT j.job_id, s.srvname, j.name, js.step_id, js.command, j.enabled FROM
searchENDELSE BEGIN SELECT o.name As “”Stored Procedures”” FROM SYSOBJECTS o INNER JOIN SYSCOMMENTS c ON o.id = c.id WHERE c.text LIKE
search AND o.xtype IN (“”P””,””FN””) GROUP BY o.name ORDER BY o.name SELECT o.name As “”Views”” FROM SYSOBJECTS o INNER JOIN SYSCOMMENTS c ON o.id = c.id WHERE c.text LIKE
search AND o.xtype = “”V”” GROUP BY o.name ORDER BY o.name SELECT t.name AS “”Tables”” FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id WHERE c.name LIKE
search ORDER BY Tables SELECT j.name AS “”Jobs”” FROM
TSQL Update: If you renamed a procedure, it only updates sysobjects but not syscomments, which keeps the old name and therefore that procedure will not be included in the search result unless you drop and recreate the procedure.
SELECT DISTINCT o.name AS Object_Name,o.type_descFROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id=o.object_idWHERE CHARINDEX(“
SELECT DISTINCT o.name AS Object_Name,o.type_descFROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id=o.object_idWHERE PATINDEX(“<<>ABD>”,m.definition) >0 ; Using this double <<>ABD> is similar to escaping :
WHERE m.definition LIKE “%<<>ABD>%”
also try this :
SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION like “%
Select distinct OBJECT_NAME(id) from syscomments where text like “%string%” AND OBJECTPROPERTY(id, “IsProcedure”) = 1
T_Find_Text VARCHAR(1000) = “Foo”IF OBJECT_ID(“tempdb..#T_DBNAME”) IS NOT NULL DROP TABLE #T_DBNAMEIF OBJECT_ID(“tempdb..#T_PROCEDURE”) IS NOT NULL DROP TABLE #T_PROCEDURECREATE TABLE #T_DBNAME( IDX int IDENTITY(1,1) PRIMARY KEY , DBName VARCHAR(255))CREATE TABLE #T_PROCEDURE( IDX int IDENTITY(1,1) PRIMARY KEY , DBName VARCHAR(255) , Procedure_Name VARCHAR(MAX) , Procedure_Description VARCHAR(MAX))INSERT INTO #T_DBNAME (DBName)SELECT name FROM master.dbo.sysdatabasesDECLARE
/* SEARCH SPROCS & VIEWS The following query will allow search within the definitions of stored procedures and views. It spits out the results as XML, with the full definitions, so you can browse them without having to script them individually.*//* STEP 1: POPULATE SEARCH KEYS. (Set to NULL to ignore)*/DECLARE
def_key varchar(128) = “%foo%”, /* root element */SELECT ( /* This inner query maps stored procedure rows to elements */ SELECT TOP 100 PERCENT
Every so often I use this script to figure out which procs to modify, or to figure out what uses a column of a table, or that table at all to remove some old junk. It checks each database on the instance it is ran on by the wonderfully supplied sp_msforeachdb.
if object_id(“tempdb..##nothing”) is not null drop table ##nothingCREATE TABLE ##nothing( DatabaseName varchar(30), SchemaName varchar(30), ObjectName varchar(100), ObjectType varchar(50))EXEC master.sys.sp_msforeachdb “USE ?insert into ##nothingSELECT db_name() AS
— Applicable for SQL 2005+USE YOUR_DATABASE_NAME //; GOSELECT
A different version, To make query more appropriate for different coding practices.
SELECT DISTINCT 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 WHERE UPPER(M.DEFINITION) LIKE UPPER(“%Your Text%”);
Highly active question. Earn 10 reputation in order to answer this question. The reputation requirement helps protect this question from spam and non-answer activity.
Not the answer you're looking for? Browse other questions tagged sql sql-server stored-procedures or ask your own question.
site design / logo © 2021 Stack Exchange Inc; user contributions licensed under cc by-sa. rev2021.5.10.39252