Place: Any Developer Shop
Scenario: A developer wants to drop a column from a table
Time: Any Day – usually right before developer wants to go home
The developer rushes to the manager and the following conversation begins:
Developer: I want to drop a column from one of the tables.
Manager: Sure, just document all the places it is used in our application and come back to me.
Developer: We only use stored procedures.
Manager: Sure, then document how many stored procedures are there which are using your column and justify the modification. I will approve it once I see the documentation.
Developer back to the desk looking at hundreds of stored procedures in SSMS thinking how to find which stored procedure may be using his column. Suddenly he remembers a bookmark which he has saved earlier which had T-SQL Script to do so. He quickly opened it and ran the code.
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'
The above T-SQL Script will search in the stored procedure text and return the name of the stored procedure if it will find the value specified in the WHERE
condition. He was happy with his discovery and immediately created the list of the stored procedures and next action items as asked by the manager. He sent the list to the manager right after 10 minutes of his discussion with the manager. He rushed to the manager at the office to inform his promptness and realized that the manager had left for the day just few moments before.
Moral of the story: Work life balance can be maintained if we work smart!
Let us see above T-SQL Script in action. Let us assume that in AdventureWorks2012
database, we want to find the BusinessEntityID
column in all the stored procedures. We can run the following T-SQL code in SSMS Query Editor and find the name of all the stored procedures.
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
The above T-SQL script will give results containing the name of the stored procedure and stored procedure text along with it.
While we are discussing this subject, here are a couple of other additional related blog posts which you may find interesting.
A question to you: Is there any better way to find a column used in a stored procedure? Please leave a comment with your solution. I will post the same in this blog with due credit.
Reference: Pinal Dave (http://blog.sqlauthority.com)
Pinal Dave is a Microsoft Technology Evangelist (Database and BI). He has written over 2200 articles on the subject on his blog at http://blog.sqlauthority.com. Along with 8+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). He is co-author of two SQL Server books - SQL Server Programming, SQL Wait Stats and SQL Server Interview Questions and Answers. Prior to joining Microsoft he was awarded Microsoft MVP award for three continuous years for his contribution in community.