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 for 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)
Hi Pinal,
I had noted the script and test it all related to search in Stored procedures and other objects. It is very useful to learn and minimize developers confusion to verify all the procedures. Earlier i am only using sp_help only.
Hello
I have one question: How can you detect your column if you have multiple tables that contain a column with your searched name
Hi Gatej,
using this below script to check a column and its table. Do few changes in this script to check a column in more than one table in database.
–Searching for Empoloyee table and ChangeDate column together
SELECT Name FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ‘%Empoloyee %’
AND OBJECT_DEFINITION(OBJECT_ID) LIKE ‘%ChangeDate%’
GO
http://blog.sqlauthority.com/2007/09/03/sql-server-2005-search-stored-procedure-code-search-stored-procedure-text/
Very informative…I learnt something new today :)
Very useful
thanks for sharing
This script search only stored procedure for 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’ + ‘%’
if the column is alias in the stored procedure . how we can search it
Can anyone tell me if there is a way to get the list of all the columns used in a stored procedure and also their table names?