Searching Stored Procedures And Ad Hoc Queries

Kevin Feasel

2017-11-15

T-SQL

Bert Wagner has a couple queries to help you find references in T-SQL objects, as well as ad hoc statements which are currently in the plan cache:

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.

Click through for the scripts.  Finding references in T-SQL objects (views, procedures, functions, triggers, etc.) is a fairly straightforward process.  Finding references in ad hoc statements is much more hit-or-miss.

Related Posts

Computing Time to Payment on Invoices

Daniel Hutmacher has a painful but realistic problem to solve: Here’s an example customer. You’ll notice right off the bat that we’re sending this customer an invoice every day on the 20th of the month. To add some complexity, the customer will arbitrarily pay parts of the invoiced amount over time, and to add insult […]

Read More

Tracking Who Changed Data

Bert Wagner is on a quest to find out who moved his cheese: Have you ever wondered who was the last person (or process) to modify a piece of data in your database? SQL Server offers plenty of system views and functions that provide insight into how your server is running and how your queries are performing. […]

Read More

Categories

November 2017
MTWTFSS
« Oct Dec »
 12345
6789101112
13141516171819
20212223242526
27282930