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 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)

12 thoughts on “SQL SERVER – Find Column Used in Stored Procedure – Search Stored Procedure for Column Name – Part 2

  1. 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.

    Like

  2. Hello

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

    Like

  3. 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

    Like

  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #038 | Journey to SQL Authority with Pinal Dave

  5. 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’+’%’

    Like

  6. 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’+’%’

    Like

  7. 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?

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s