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

Generating SQL With Biml

Cathrine Wilhelmsen shows us you can do a lot more with Biml than just generating SSIS packages: This actually happened to me in a previous job. We had a fairly complex ETL solution for the most critical part of our Data Warehouse. Many SSIS packages, views, and stored procedures queried the tables that were replicas […]

Read More

Rowcount Shenanigans When Deleting In Batches

Denis Gobo takes us through a few issues you might run into when deleting data in batches: I have always used WHILE @@rowcount > 0 but you have to be careful because @@rowcount could be 0 when your while loop starts Let’s take a look at an example. This is a simplified example without a where […]

Read More

Categories

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