Have you ever found yourself in a situation where you need to drop a column from a table in SQL Server, but you’re not sure which stored procedures are using that column? Don’t worry, you’re not alone. In this article, we will discuss a simple method to find a column used in a stored procedure.

Let’s say you’re a developer working in a busy developer shop. You need to drop a column from one of the tables, but before you can do that, your manager asks you to document all the places where that column is used in your application. You quickly realize that your application only uses stored procedures, so you need to find all the stored procedures that reference the column.

One way to accomplish this is by using a T-SQL script. Here’s an example:

SELECT obj.Name SPName, sc.TEXT SPText
FROM sys.syscomments sc
INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID
WHERE sc.TEXT LIKE '%' + 'Name Your Column Here' + '%' AND TYPE = 'P'

In the above script, replace ‘Name Your Column Here’ with the name of the column you want to find. This script will search the stored procedure text and return the name of the stored procedure if it finds the specified column.

Once you have the list of stored procedures, you can document them and justify the modification to your manager. This will help you get the approval you need to drop the column.

Now, let’s see this script in action. Assume that we want to find the ‘BusinessEntityID’ column in all the stored procedures in the AdventureWorks2012 database. We can run the following T-SQL code in SSMS Query Editor:

USE AdventureWorks2012
GO

SELECT obj.Name SPName, sc.TEXT SPText
FROM sys.syscomments sc
INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID
WHERE sc.TEXT LIKE '%' + 'BusinessEntityID' + '%' AND TYPE = 'P'
GO

This script will give you the results containing the name of the stored procedure and the stored procedure text that references the ‘BusinessEntityID’ column.

While we have discussed a simple method to find a column used in a stored procedure, there might be other ways to achieve the same result. If you have a better solution, please leave a comment below with your solution. We will update this blog post with your solution and give you due credit.

Remember, working smart can help you maintain a work-life balance. So, the next time you need to find a column used in a stored procedure, use this simple method and save yourself some time and effort.