How can I search the full text of a stored procedure for a value?
I use the following script to search the text of all stored procedures when I want to find specific values.
I recently discovered that ROUTINE_DEFINITION stops after 4000 characters, so some procedures were not getting returned when they should have been.
How can I query the full text of a stored procedure for a value?
4 Answers 4
From BOL, on INFORMATION_SCHEMA.ROUTINES :
Column name: ROUTINE_DEFINITION
Data type: nvarchar(4000)
Description: Returns the first 4000 characters of the definition text of the function or stored procedure if the function or stored procedure is not encrypted. Otherwise, returns NULL. To ensure you obtain the complete definition, query the OBJECT_DEFINITION function or the definition column in the sys.sql_modules catalog view.
You can therefore view the rest by changing you script as follows:
The column aliased above as FullDefinition will return the rest of the content as nvarchar(max) .
SQL Server: Search And Find Stored Procedure
Several times in the past, I’ve come across situations that needed to find stored procedure by part of its name. I could not remember the exact name of the stored procedure. Instead I knew only part of its name. The databases had thousands of stored procedures. In such situations, searching and finding a stored procedure is difficult. But, fortunately we have several methods to find the stored procedures in SQL Server by its name, part of the name, having a text or having the tables or column used in it. Now, we’ll see the methods one by one.
I. Find Stored Procedure By Its Name Or Partial Name
Find Using Select Query Against:
To find a stored procedure using it full name or partial name, we can use simple select queries against the system tables like sys.procedures, Information_Schema.Routines or syscomments. Below are the simple select queries. In all the below queries, change the Employee text to your search text
Sys.Procedures
sys.procedures system table has the name of the procedure, object id and other details like created date, modified date, etc… So, if you know part of the stored procedure’s name, then you can use this option to find the list of stored procedures having the phrase in the name.
INFORMATION_SCHEMA.ROUTINES
INFORMATION_SCHEMA.ROUTINES is a system information schema view. This system view can be used to fetch all the details about the stored procedure like, name, definition / source code, database schema, created and modified date, etc.. You can use this view to search for stored procedure by its full or partial name and the stored procedures containing text. For now I’ll give you the query to find the stored procedure by its name. Later on in this article you will see about using INFORMATION_SCHEMA.ROUTINES to search stored procedure containing text.
Sys.SysComments
sys.syscomments is a system compatibility view. This system view can be used to fetch the object id and the definition / source code of stored procedures, views, trigger, rule and other programmable objects within the database.
Sys.Sql_Modules
sys.sql_modules is a system object catalog view. This system view can be used to fetch the user defined programmability object’s definition / source code.
Find Using Filter Settings In Object Explorer
Another simple way to find the stored procedure by its full or partial name is by using the filter settings option in SQL Server Management Studio Object Explorer. Below are the steps for using filter settings to find stored procedure.
- In the Object Explorer in SQL Server Management Studio, go to the database and expand it.
- Expand the Programmability folder.
- Right Click the Stored Procedures folder.
- From the right-click menu, select Filter in the right-click menu.
- Under filter, select Filter Settings. The filter settings window will pop up.
- In the filter settings window against the property Name, you can choose one of the Operator from the list (Equals, Contains, Does not contain) and enter the search phrase (or stored procedure’s name or partial name) under Value and then click OK.
- The list of stored procedures are filtered based on your filter criteria. Now expand the Stored Procedures folder. You can see only the stored procedures which fall under the filter criteria.
II. Find Stored procedure Containing Text Or Table Name
Just like searching for stored procedure by its name, you can also search and find stored procedures in SQL Server by the text or phrase or table name or column name used in the definition (source code). 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.
Below are few of the methods. 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.
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.
INFORMATION_SCHEMA.ROUTINES
Sys.SysComments
Sys.Sql_Modules
Related Article
- You may be interested in finding the last few executed queries in SQL Server. (User Defined Functions). . .
Reference
If you know of any other methods to find stored procedure in SQL Server, please share it through the comments section below.
How do I find a stored procedure containing <text>?
I need to search a SQL server 2008 for stored procedures containing where maybe the name of a database field or variable name.
21 Answers 21
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.
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:
You can also try ApexSQL Search — trial version SSMS plug-in from ApexSQL.
In case you needed schema as well:
First ensure that you’re running the query under your user credentials, and also in the right database context.
How Do I Find A String In Any Stored Procedure
This is an article about references and looking for clues. I originally wanted to title this article “Dude, Where’s My Code” but I had a feeling that:
A: Not many would catch the 1990’s movie reference.
B: I never actually saw “Dude, Where’s My Car” and I’m likely better for it.
C: I was having a hard time handling the fact that people would not get a reference to a 1990’s movie and that I’d just end up feeling really, really old.
But when it comes for trying to figure out where we used specific code or where we made reference to a specific object you will understand why I created this stored procedure the article focuses on as we proceed.
A Not-As-Brief-As-It-Should-Be Background on the Why
Long ago, one of my first articles focused on searching for specific columns to identify what table (or tables) a column may reside within a database using the sys.all_columns system catalog view. (LINK)
The reasoning: as a DBA working in healthcare I had to support over 2,000 separate databases developed both internally and by hundreds of independent software vendors (ISVs). Each development company – and Developer within a company – has her or his own style of coding. Finding issues within code that you’re not familiar with is one of those responsibilities you find yourself involved in as a data professional on a regular basis. The gist of that early article was identifying where columns existed for the purpose of debugging an issue affecting uptime of a healthcare product.
Now here I am years later in a new role at a new company. I find myself still using the code from that article but I also needed to come up to speed with how the development teams are utilizing stored procedures throughout the product since I now had responsibility over not just database administration but engineering and coding in my new joint role as both a DBA and DBE (Engineer).
Creating a search script to identify where specific objects, coding structures, or predicates were involved was critical for me to troubleshoot issues and develop new code.
A Stored Procedure to Search Stored Procedures
I look at coding like I do presenting: there should be a goal of what you want to accomplish for the time you’re spending when creating content of any kind. In this case the goal is to create a stored procedure that can be used to hunt for any reference to an object, code structure, etc. All that matters is that you’ve a string you want to search throughout all databases to return results for any stored procedure using the string we’re passing in as a variable for searching.
Just knowing the stored procedure name is not good enough. Therefore the end result will also include the following columns:
- database name
- schema name
- stored procedure name
- stored procedure code
In order to build out this stored procedure we need to rely upon a few system objects: two system catalog views and one undocumented system stored procedure.
System Catalog Views:
sys.procedures – this system view provides metadata about all stored procedures in a database. It’s database-scoped which means each database has a sys.procedures catalog view and it only returns results for the contents of the database in which it is hosted.
sys.syscomments – this system view stores the code text for each stored procedure. As is the case with sys.procedures, sys.comments is also database-scoped so it will only provide insight into its own database’s objects.
Un-Documented System Stored Procedure:
sys.sp_MSforeachdb — Since the two system catalog views are database-scoped we need a way to return results for this query against all databases. Microsoft SQL Server comes with an undocumented stored procedure in the master database that provides this functionality to do just that. You can pass in a parameter for the t-sql command you want to run against each database and results are returned as separate record sets for each database the code is executed against.
Putting it All Together
Now that we know what needs to go into the solution it’s time to look at how we put it all together. Let me just lay out the code below then we can look at the particulars and see an example of this in action.
Breaking this down the first thing you’ll likely notice is that I’m creating this stored procedure in a database called iDBA. Anyone who reads my articles over the years knows that this is the database I use for all my administrative scripts. You may have a different name for this database in your environments but it’s important to have a database you can use for such needs in order to keep non-system objects from leaking into the master database where lazy coders and DBAs who love shortcuts drop their code.
I’ve required a single parameter for this stored procedure to pass in the string you wish to search for as @searchforthis. Inside the script I’ll bookend the parameter with % symbols so you don’t need to go overboard with the search string if you know you can get to your needed results with a smaller block of search criteria.
This stored procedure will search through all databases so I want to create a temp table to store the results that will then get returned to the end user. That’s what #search_results is for. While the first four columns make sense at face value, the final column (colid) may seem odd. The reason we need to have that column (sourced from sys.syscomments) is because stored procedures can be simple or quite complex. The code that comprises a stored procedure may need to be stored in multiple rows in sys.syscomments as a result. Inside of sys.syscomments you can identify the object the text of a stored procedure belongs to by looking at the id column. It maps to object_id throughout the system tables. The colid value is a 1-based column that refers to the order of the comment(s) that comprise the full text of a stored procedure. If you were to query sys.syscomments directly you may see multiple rows for which reference some object_id for a stored procedure. Each row for in sys.syscomments will have an escalating value for colid that orders the full text of the stored procedure so colid = 1 would be followed by colid = 2 and colid = 3… until the full command that comprises the stored procedure is covered.
So what this stored procedure ultimately does is builds a dynamic query joining sys.procedures and sys.syscomments on object_id to id (as explained above) where the comments contain the search criteria you’re hoping to find. This query text is then passed into the sp_MSforeachdb undocumented stored procedure as a parameter and executed one database at a time. All “hits” for the databases on your server get dropped into a temp table and returned when all databases have been queried.
Seeing it in Action
After creating the stored procedure I’m going to run the following query. Since I specify the database name it doesn’t matter what database is currently active when I run the script:
In this case I’m using ‘backupset’ because I know I’ve written stored procedures in multiple databases that reference that view in the msdb database for identifying backup metadata. I also expect to get hits in this case in system databases because I’m using a system view’s name as the search text.
The results look something like this:
Conclusion
While there are third party tools available to provide the same functionality as what we did here. Not everyone has the budget to purchase them. This is a simple code construct that is quite extensible and can save significant amounts of time when you’re searching for references throughout any number of databases on a SQL Server.