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 (https://blog.sqlauthority.com)
19 Comments. Leave new
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
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?
For Store Procedures as well as for Table Findings
SELECT s1.name , ‘table’ type
FROM SYS.OBJECTS s1
INNER JOIN SYS.COLUMNS s2
ON s1.OBJECT_ID = s2.OBJECT_ID
WHERE s2.name = ‘remarks’ –Colum Name
and s1.type = ‘U’
UNION
SELECT s1.name , ‘procedure’ type
FROM SYS.PROCEDURES s1
INNER JOIN SYS.SQL_MODULES s2
ON s1.object_id = s2.object_id
AND S2.DEFINITION LIKE ‘%’+’remarks’+’%’
For Store Procedures as well as for Table Findings
SELECT s1.name , ‘table’ type
FROM SYS.OBJECTS s1
INNER JOIN SYS.COLUMNS s2
ON s1.OBJECT_ID = s2.OBJECT_ID
WHERE s2.name = ‘remarks’ –Colum Name
and s1.type = ‘U’
UNION
SELECT s1.name , ‘procedure’ type
FROM SYS.PROCEDURES s1
INNER JOIN SYS.SQL_MODULES s2
ON s1.object_id = s2.object_id
AND S2.DEFINITION LIKE ‘%’+’remarks’+’%’
Hi Dave, I have a question. I have a view with some fields, joins and also some fields has an alias. Now I need the information about the sourcefield, sourcetable and also the alias. Is there a way to get this?
Really Useful..Today i learned a new concept..
If i have a column name “Tag” which i want to find it returns a function with Column Name “DTag”.
this is more simpler
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ‘%YourCulomnName%’
This article isn’t really about finding a column – more like a string in a stored proc. If you create a proc with the body of “declare @junkstring int”, and then the above querys, looking for junk, or string, or kstr, the proc will be returned, even though this is a variable, not a column.
True Dave. Do you have better script to get same details? please share and I would be happy to blog with due credit to you.
What if the Stored Procedure has more than 8000 characters? not sure it will find it
Never tested it.
this reply is kinda late, but if you have that big Stored Procedure, maybe you need to revisit and consider breaking it up to multiple (smaller) SPs. I’m sure you can separate certain area from the code and just get its output in a separate SP.
ORA-00911: invalid character
00911. 00000 – “invalid character”
*Cause: identifiers may not start with any ASCII character other than
letters and numbers. $#_ are also allowed after the first
character. Identifiers enclosed by doublequotes may contain
any character other than a doublequote. Alternative quotes
(q’#…#’) cannot use spaces, tabs, or carriage returns as
delimiters. For all other contexts, consult the SQL Language
Reference Manual.
*Action:
Error at Line: 2 Column: 41
I’m receiving the above error to the execution of above scripts