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

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 testingSmoke testingRegression testingFunctional testingIntegration testingUser Acceptance TestingPerformance 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 their expectations. In our example: This test w…

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 representing som…

5 Steps to Install Robot Framework for Web Testing Automation - 7 minute Guide

Lately, I found myself helping several people to begin their automation testing journey. And what a better place to do it then a nice new installation of a Robot Framework with its Selenium2Library on a Windows OS.

Why Robot Framework with Selenium2Library? While you are probably here because you are looking for a quick installation guide for Automation Testing platform for you web project, I still need to do my do diligence and explain in couple of words why we need it.
Robot Framework it's a Python framework that allows for a less development oriented Manual QA professionals to do a quick switch into Test Automation. This can be achieved by allowing for easy English phrases (e.g. Click Element, Wait, etc) to replace a more complex Python syntax.
Selenium2Library it's additional add on on top of Robot Framework that allows easy interaction with web elements during the automation process.

That's it..... DONE with the talking now the installation....

Installation process Ro…