Earlier this week I wrote a blog about Find Column Used in Stored Procedure – Search Stored Procedure for Column Name. I received plenty of comments on the subject. One of the statements which I used in the story (Time: Any Day – usually right before developer wants to go home) was very much liked by many developers. I guess this is because we are all like the same. We often get more work, when we are ready to go home. After reading the blog post many readers and SQL Server Experts have posted an enhanced T-SQL script to find column used in a stored procedure.
SQL Server Expert Imran Mohammed is a very good friend of mine. He posted a very interesting note that function used in the original script is going to be deprecated in future releases so better to use following scripts.
1) Search in All Objects
This script search stored procedures, views, functions as well other objects.
-- Search in All Objects SELECT OBJECT_NAME(OBJECT_ID), definition FROM sys.sql_modules WHERE definition LIKE '%' + 'BusinessEntityID' + '%' GO
2) Search in Stored Procedure
This script search only stored procedure in the specified column.
-- Search in Stored Procedure Only SELECT DISTINCT OBJECT_NAME(OBJECT_ID), object_definition(OBJECT_ID) FROM sys.Procedures WHERE object_definition(OBJECT_ID) LIKE '%' + 'BusinessEntityID' + '%' GO
Thanks Imran for suggesting this follow up script. Btw, if you want to read a short story, I suggest you head to original blog post.
Reference: Pinal Dave (http://blog.sqlauthority.com)