Skip to main content

SQL Server - How to search in all Databases for Stored Procedure that uses specific table

My team and I were recently faced with a challenge that required us to search the database for a stored procedure that contains reference for a specific table or another stored procedure. We were able to accomplish that by a simple text search query that uses the system object definition and goes like that:

SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules (nolock)
WHERE definition like '%----SOME TEXT HERE----%'

The interesting part begins once we were faced with a more complex system structure that included stored procedures which can be called from different databases and include inserts, updates and selects to yet another set of databases. Of curse executing the same query again and again for each database to perform the required search was not an option so the solution presented itself in an undocumented system stored procedure called sp_MSforeachtable.
This stored procedure allows us execute the same query for all the databases on the server with "?" used as database name. the final resulting query is the following:

EXECUTE master.sys.sp_MSforeachdb 'USE [?];
SELECT "?" as DBName,OBJECT_NAME(object_id)
FROM ?.sys.sql_modules (nolock)
WHERE definition like ''%----SOME TEXT HERE----%'''

If anyone can suggest a better more efficient way to perform that task please share in the comments.

Comments

Popular posts from this blog

Proper QA estimation in Agile project

  Today, I want to chat about a common issue in Agile development – story point estimation. You see, it's easy to get caught up in estimating how long a feature will take to develop and forget about the crucial Quality Assurance (QA) effort.  It happens to often: your team is in the middle of sprint planning. New user stories are being discussed, and estimations are given, but there's a catch. You've estimated the development time perfectly, but you've barely even glanced at how much effort QA will take. Why? Agile talks about Development Team and you mistakenly thought that it is all about DEVELOPMENT. Sound familiar? Trust me; it's a more common scenario than you might think. So, what's the big deal? Well, when you leave QA effort out of the equation, several not-so-great things can happen: Vilocity can suffer: You might think you can squeeze more into a sprint than is humanly possible, setting your team up for disappointment and overcommitment. Quality can de

Performance testing vs Load testing vs Stress testing

Today I want to discuss a popular topic regarding a difference between Load and Performance testing. Those two types of testing are commonly used together but there are several key differences between the two. To get a better understanding of the topic lets have a real life example from one of my clients and use it to explain the difference. I have worked for a client that was building an in-house web application that provides its customers with an option to select and order different products and services. The request was, before the up-coming release, to test the performance of their product. We started the task by trying and understand what they expect from performance point of view and then went through an exercise of defining what is captured by what test. The client said that they are looking to have about 900-1300 active users on the site at a given moment, and the expected response time (for a page to load) should be less than 5 seconds. With those details what are the t

What is the velocity of an Agile scrum methodology?

Let's discuss some of the important measurements in Agile, and that is the Velocity of the Scrum team work. Based on Wikipedia definition Velocity is " ...the rate of change of its position with respect to a frame of reference and is a function of time...", which when transferred to the scrum world can be summarized as: The amount of work that the scrum team completed in a single measure of time - in a sprint. How we Calculate Velocity? Velocity is actually a very simple to calculate, it is done but totaling the number of story points of fully completed user stories from the sprint backlog. So if a current sprint included 4 user stories: 2 with 8 story points each, one with 3 story points and one with 32 story points. and by the end of the sprint the 32 one was not fully done the velocity calculation will be: 8+8+3=19 Note: the 32 story points are not part of the velocity calculation as this user story was not completed. What Velocity is used for? The v