SQL SERVER – Find Column Used in Stored Procedure – Search Stored Procedure for Column Name – Part 2

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 - Find Column Used in Stored Procedure - Search Stored Procedure for Column Name - Part 2 findsp-500x242

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)

, ,
Previous Post
SQL SERVER – Generate Script for Schema and Data – SQL in Sixty Seconds #021 – Video
Next Post
SQL SERVER – INFORMATION_SCHEMA.COLUMNS and Value Character Maximum Length -1

Related Posts

19 Comments. Leave new

  • Kalyanasundaram.K
    July 19, 2012 10:18 am

    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.

    Reply
  • Gatej Alexandru
    July 19, 2012 11:55 am

    Hello

    I have one question: How can you detect your column if you have multiple tables that contain a column with your searched name

    Reply
  • Very informative…I learnt something new today :)

    Reply
  • Very useful
    thanks for sharing

    Reply
  • 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

    Reply
  • 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?

    Reply
  • 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’+’%’

    Reply
  • 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’+’%’

    Reply
  • 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?

    Reply
  • Really Useful..Today i learned a new concept..

    Reply
  • If i have a column name “Tag” which i want to find it returns a function with Column Name “DTag”.

    Reply
  • this is more simpler
    SELECT Name
    FROM sys.procedures
    WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ‘%YourCulomnName%’

    Reply
  • 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.

    Reply
    • 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.

      Reply
  • What if the Stored Procedure has more than 8000 characters? not sure it will find it

    Reply
    • Never tested it.

      Reply
    • 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.

      Reply
  • 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

    Reply

Leave a Reply

Menu