2013-02-05 09:30:39 8 Comments
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?
Related Questions
Sponsored Content
39 Answered Questions
[SOLVED] Add a column with a default value to an existing table in SQL Server
- 2008-09-18 12:30:04
- Mathias
- 2734112 View
- 2662 Score
- 39 Answer
- Tags: sql sql-server sql-server-2005 sql-server-2000
29 Answered Questions
[SOLVED] Insert results of a stored procedure into a temporary table
- 2009-03-17 10:45:26
- Ferdeen
- 1895931 View
- 1530 Score
- 29 Answer
- Tags: sql sql-server sql-server-2005 tsql stored-procedures
41 Answered Questions
[SOLVED] How to return only the Date from a SQL Server DateTime datatype
- 2008-09-22 03:31:33
- eddiegroves
- 2661983 View
- 1700 Score
- 41 Answer
- Tags: sql sql-server tsql date datetime
28 Answered Questions
[SOLVED] How can I prevent SQL injection in PHP?
- 2008-09-12 23:55:00
- Andrew G. Johnson
- 1667524 View
- 2776 Score
- 28 Answer
- Tags: php mysql sql security sql-injection
30 Answered Questions
[SOLVED] How to check if a column exists in a SQL Server table?
- 2008-09-25 12:34:00
- Maciej
- 1155282 View
- 1796 Score
- 30 Answer
- Tags: sql-server sql-server-2008 tsql sql-server-2012 sql-server-2016
33 Answered Questions
[SOLVED] How do I UPDATE from a SELECT in SQL Server?
- 2010-02-25 14:36:53
- jamesmhaley
- 4101345 View
- 3552 Score
- 33 Answer
- Tags: sql sql-server tsql select
47 Answered Questions
[SOLVED] How to concatenate text from multiple rows into a single text string in SQL server?
- 2008-10-11 23:49:59
- JohnnyM
- 2223149 View
- 1826 Score
- 47 Answer
- Tags: sql sql-server csv string-concatenation group-concat
19 Answered Questions
[SOLVED] Function vs. Stored Procedure in SQL Server
- 2009-07-24 19:40:06
- Tarik
- 642830 View
- 803 Score
- 19 Answer
- Tags: sql sql-server tsql stored-procedures sql-function
12 Answered Questions
[SOLVED] SQL Server: Query fast, but slow from procedure
- 2009-01-13 21:49:11
- Ian Boyd
- 181257 View
- 247 Score
- 12 Answer
- Tags: sql-server performance stored-procedures
10 Answered Questions
[SOLVED] SQL Server: How to Join to first row
- 2010-01-11 16:44:37
- Ian Boyd
- 524965 View
- 722 Score
- 10 Answer
- Tags: sql sql-server tsql sql-server-2000
22 comments
@Atahan Ceylan 2017-06-29 11:12:14
OBJECT_NAME([id]) --> Object Name (View,Store Procedure,Scalar Function,Table function 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
@Weihui Guo 2017-08-14 19:07:44
I created a procedure to search text in procedures, tables, views, or jobs. The first parameter @search is the search criterion, @target is the search target, i.e., procedures, tables, etc. If not specified, search all. @db is to specify the database to search, if not specified, use current db. Here is the query in dynamic SQL.
@Weihui Guo 2018-10-31 12:20:58
Follow this link to search column name.
@Weihui Guo 2019-08-29 17:43:56
Use
(o.xtype = ''P'' OR o.xtype = ''FN'')
to search function as well@Mahmoud Gamal 2013-02-05 09:33:42
Escape the square brackets:
Then the square brackets will be treated as a string literals not as wild cards.
@Imad 2015-08-12 04:14:35
doen't give schema name :(
@patricus 2016-07-07 15:07:56
@Imad You can get the schema name by adding
SCHEMA_NAME(o.schema_id) AS Schema_Name
to the select clause.@Charles Graham 2016-07-14 15:59:59
This doesn't work with longer stored procedures. Use Ullas's SQL below instead.
@Hunter Nelson 2017-03-15 16:21:09
I found out the hard way that this answer will only search the first 4000 characters of stored procedure text. Check out this link for the real answer. sqlhints.com/2011/10/01/…
@Eddie Kumar 2017-09-18 11:54:04
Same here, information_schema didn't search content-text (within SProcs), instead use of "sys.procedures" worked for me. I understand that Microsoft recommends the use of information_schema, however apparently this is not working for many, hence Microsoft should look into this issue.
@Eghbal Sohrabi 2018-05-23 05:46:30
SELECT OBJECT_NAME(object_id), OBJECT_DEFINITION(object_id) FROM sys.procedures WHERE OBJECT_DEFINITION(object_id) LIKE '%[ADB]%'
@sdlins 2019-05-09 21:20:04
@patricus Do you know some way to get the DB name besides the schema name? Thanks
@Devin Lamothe 2019-11-12 00:16:02
fun fact: _ is a wildcard too in LIKE expressions!
@A.D. 2017-11-04 11:27:15
also try this :
@Nokcha 2017-10-13 02:12:30
This query is search text in stored procedure from all databases.
@Abhinav Sharma 2017-07-23 14:06:36
@Milan 2017-01-24 19:36:00
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 select
Tasks -> 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 :)
@user27332 2013-08-21 14:39:19
Try this request:
Query
@jesterm 2016-12-07 15:37:54
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.
@Bitcoin Murderous Maniac 2017-05-03 22:31:36
oh, ya... is that so!
@Gopakumar N.Kurup 2015-11-26 05:33:18
@pedram 2015-11-10 13:23:10
Good practice to work with SQL Server.
Make below store procedure and set short key for that like bottom image,
Now - 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
andTables
. You just need to write that keyword and press short key.For example: I want to search 'PaymentTable' then write 'PaymentTable' in query editor and press short key
ctrl+4
- it will provide you full result.@Anil Singh 2015-06-11 06:30:19
It might help you!
@groggyjava 2015-07-02 16:32:12
@Kamruzzaman 2014-09-25 13:35:21
You can also use
and then run
@Roger Willcocks 2015-05-07 22:16:49
The WHERE clause is missing a field reference. You should also reference the schema (sys)
@G R 2015-03-24 08:31:47
@steoleary 2013-11-03 07:56:28
Redgate's SQL Search is a great tool for doing this, it's a free plugin for SSMS.
@user2846553 2013-10-04 12:28:16
You can also use this one:
@Shannon Severance 2014-11-18 21:55:50
-1 Downside compared to other options is
INFORMATION_SCHEMA.ROUTINES.ROUTINE_DEFINITION
only holds first 4000 characters of the routine.@David Smithers 2013-09-05 10:55:19
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.
@user1001101 2013-05-22 07:42:34
@Daniel RG 2013-03-26 16:25:35
Also you can use:
Thats include comments
@jlnorsworthy 2013-08-06 18:28:29
You shouldn't use syscomments for this purpose. The text field cuts off at 4000 characters. The definition field from sys.sql_modules seems to store the entire text (more than 4000 anyway)
@bonCodigo 2013-02-05 09:34:07
Using CHARINDEX:
Using PATINDEX:
Using this double
[[]ABD]
is similar to escaping :@Daniel Kelley 2013-02-05 09:33:10
I usually run the following to achieve that: