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)
