Search text in stored procedure in SQL Server
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 '%[ABD]%';
I want to search for
[ABD] in all stored procedures including square brackets, but it's not giving the proper result. How can I change my query to achieve this?
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.
Read more… Read less…
Try this request:
SELECT name FROM sys.procedures WHERE 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 '%[ABD]%' order by object_name(id)
Good practice to work with SQL Server.
Create below stored procedure and set short key,
CREATE PROCEDURE [dbo].[Searchinall] (@strFind AS VARCHAR(MAX)) AS BEGIN 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 '%'[email protected]+'%' 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 '%'[email protected]+'%' 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 '%'[email protected]+'%' 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 '%'[email protected]+'%' ORDER BY Table_Name END END
Now - Set short key as below,
So next time whenever you want to find a particular text in any of the four objects like
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.