How to Search Stored Procedures and Ad-Hoc Queries

Photo by Louis Blythe on Unsplash

Have you ever wanted to find something that was referenced in the body of a SQL query?

Maybe you need to know what queries you will have to modify for an upcoming table rename.  Or maybe you want to see how many queries on your server are running  SELECT *

Below are two templates you can use to search across the text of SQL queries on your server.

Prefer learning by watching?  Check out this week’s post on YouTube.

1. Searching Stored Procedures, Functions, and Views

If the queries you are interested in are part of a stored procedure, function, or view, then you have to look no further than the  sys.sql_modules view.

This view stores the query text of every module in your database, along with a number of other properties.

You can use something like the following as a template for searching through the query texts of these database objects:

For example, I recently built a query for searching stored procedures and functions that might contain SQL injection vulnerabilities.

Using the starting template above, I added some filtering in the WHERE clause to limit my search to queries that follow common coding patterns that are vulnerable to SQL injection:

2. Searching Ad-Hoc SQL Queries

Searching across ad-hoc queries is a little tougher.  Unless you are proactively logging the query texts with extended events or some other tool, there is no way to definitively search every ad-hoc query text.

However, SQL Server does create (or reuse) an execution plan for each query that executes.  Most of those plans are then added to the execution plan cache.

Execution plans are eventually removed from the cache for various reasons, but while they exist we can easily search their contents, including searching through that plan’s query text.

As a starting point, you can use the following code to retrieve SQL query texts that are currently stored in the plan cache:

Although the template above searches for the query texts in our execution plans, you can also use it to search for other query plan elements, such as elements that indicate if you have non-sargable query.

I used this technique recently to search for ad-hoc queries that might be vulnerable to SQL injection.  I modified the template above to search the input parameter values instead of the query texts, flagging any values that look like they might have some injection code in them:

So while using this technique won’t allow you to search across 100% of ad-hoc queries, it should be able to search the ones that run most frequently and appear in your plan cache.

Intrigued by how I’m searching query texts for SQL injection vulnerabilities? Attend my online webcast on Tuesday November 14, 2017 at 1PM Eastern at the PASS Security Virtual Group to learn about these queries and how protect yourself from SQL injection.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!