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:

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.


Popular posts from this blog

Story Points estimation for Scrum with Fibonacci vs Shirt Sizes vs Linear - 7 minute guide

It is all began long time ago when Development Teams were constantly asked to provide estimate and they were having a hard time to properly face the task. Let's admit it, there are so many things that can change, happen, and simply go wrong during the development process that one can hardly expect a proper estimation of hours for each task. That why a relative estimation with Story Points came along. Story Points Estimation Its a different way to estimate the effort of the Scrum Development Team with-in Agile methodology, which means that instead of estimating hours of work the team estimates each effort relatively to other efforts in the project. Let's assume that a developer knows that specific 'Task 1' is much harder than another 'Task 2' it is hard for him/her to quantify that harder feeling in hours of additional work but it is possible to say that it much more work. This situation is being address by Story Points when each story point is

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

7 Most Popular Test Types in Software Testing

Today we are going to return back to basics of software testing and discuss the 7 most popular test types that are being used in every software testing effort. Those different test types cover all the levels of the software to make sure that the final result matching the expectations from every possible angle. Here is our list: Unit testing Smoke testing Regression testing Functional testing Integration testing User Acceptance Testing Performance Testing Now let’s have a deeper dive into each one of those by using a simple example of an imaginary system that was created in order to manage warehouse activity including shipments, inventory and goods receptions from suppliers. Unit Testing This type of testing is usually performed by the developers and is covering the very basic development component. In this test developers are testing the straight forward functionality of a functional piece of code to make sure that it is performing according to th