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

Place: Any Developer Shop

Scenario: A developer wants to drop a column from a table

Time: Any Day – usually right before developer wants to go home

The developer rushes to the manager and following conversation begins:

Developer: I want to drop  a column from one of the tables.

Manager: Sure, just document it where all the places it is used in our application and come back to me.

Developer: We only use stored procedures.

Manager: Sure, then documented how many stored procedures are there which are using your column and justify the modification. I will approve it once I see the documentation.

Developer back to the desk looking at hundreds of stored procedures in SSMS thinking how to find which stored procedure may be using his column. Suddenly he remembers a bookmark which he has saved earlier which had T-SQL Script to do so. Here quickly opened it and run the code.

SELECT obj.Name SPName, sc.TEXT SPText
FROM sys.syscomments sc
INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID
WHERE sc.TEXT LIKE '%' + 'Name Your Column Here' + '%'
AND TYPE = 'P'

Above T-SQL Script will search in the stored procedure text and return the name of the stored procedure if it will find the value specified in the WHERE condition. He was happy with his discovery and immediately created the list of the stored procedures and next action items as asked by the manager. He sent the list to the manager right after 10 minutes of his discussion with the manager. He rushed to manager to office to inform his promptness and realized that the manager had left for the day just few moments before.

Moral of the story: Work life balanced can be maintained if we work smart!


Let us see above T-SQL Script in action. Let us assume that in AdventureWorks2012 database we want to find the BusinessEntityID column in all the stored procedure. We can use run following T-SQL code in SSMS Query Editor and find the name of all the stored procedure.

USE AdventureWorks2012
GO
SELECT obj.Name SPName, sc.TEXT SPText
FROM sys.syscomments sc
INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID
WHERE sc.TEXT LIKE '%' + 'BusinessEntityID' + '%'
AND TYPE = 'P'
GO

Above T-SQL script will give results containing the name of the stored procedure and stored procedure text along with it.

While we are discussing this subject here are a couple of other additional related blog post which may interesting.

A question to you: Is there any better way to find column used in a stored procedure? Please leave a comment with your solution. I will post the same in this blog with due credit.

Reference: Pinal Dave (http://blog.sqlauthority.com)

26 thoughts on “SQL SERVER – Find Column Used in Stored Procedure – Search Stored Procedure for Column Name

  1. Hi Pinal,

    I always use below method to search column or content which I required in the procedure.

    SELECT
    Object_Name(Object_Id)
    FROM
    sys.sql_modules
    WHERE
    definition LIKE ‘%Your column name%’
    AND definition LIKE ‘% Procedure %’

    sys.sql_modules:
    Returns a row for each object that is an SQL language-defined module. Objects of type P, RF, V, TR, FN, IF, TF, and R have an associated SQL module. Stand-alone defaults, objects of type D, also have an SQL module definition in this view. For a description of these types, see the type column in the sys.objects catalog view.

    Reference: http://msdn.microsoft.com/en-us/library/ms175081.aspx

    Thanks

    Like

  2. Ahh…. I see some one already posted it.. but anyways…

    –================================
    SQL Server 2005 and above
    –================================

    — Below searches all objects (Procedures, views, triggers, functions)
    Select distinct object_name(object_id)
    from sys.all_sql_modules
    Where definition like ‘%search text%’

    — If you just want to search stored procedures.
    Select distinct object_name(object_id)
    from sys.Procedures
    Where object_definition(object_id) like ‘%search text%’

    –================================
    SQL Server 2000 and below
    –================================
    — Below searches in definition of all objects (Views, triggers, functions)
    Select distinct Object_Name(id)
    From Syscomments
    Where text like ‘%search text%’

    ~ IM.

    Like

    • The below 2 queries are not working in SQL 2012
      – Below searches all objects (Procedures, views, triggers, functions)
      Select distinct object_name(object_id)
      from sys.all_sql_modules
      Where definition like ‘%search text%’

      – If you just want to search stored procedures.
      Select distinct object_name(object_id)
      from sys.Procedures
      Where object_definition(object_id) like ‘%search text%’

      Like

  3. to search syscomments is a very inefficient way and should be avoided.

    I will suggest this script as below:

    Declare @TableName sysname;
    Declare @SchemaName sysname;
    Declare @FullTableName nvarchar(256);
    Declare @ColumnName sysname;

    Set @SchemaName = ‘dbo’ –change your schema name
    Set @TableName = ‘Your_Table’ –change your table name
    Set @FullTableName = QuoteName(@SchemaName) + ‘.’ + QUOTENAME(@TableName)
    Set @ColumnName = ‘Col_Name’ –change your column name

    SELECT QuoteName(A.referencing_schema_name) + ‘.’ + QUOTENAME(A.Referencing_entity_name) As Object_Name
    , O.type_desc As Object_Type
    , M.definition As Object_Definition
    FROM sys.dm_sql_referencing_entities (@FullTableName, ‘Object’) A
    Inner Join
    sys.objects O
    On A.referencing_id = O.object_id
    Inner Join
    sys.sql_modules M
    On A.referencing_id = M.object_id
    CroSS Apply
    (
    Select *
    From sys.dm_sql_referenced_entities(QuoteName(A.referencing_schema_name) + ‘.’ + QUOTENAME(A.Referencing_entity_name), ‘Object’) B
    Where B.referenced_minor_name = @ColumnName
    ) C

    Like

  4. Another very important “feature” of syscomments is that it contains only 4000 characters per record. If your object contains more than that, it will be broken up in multiple records. There’s a good chance that an word will be seperated between those 2 records and that you will be missing a dependency… Much safer to use sql_modules!

    Like

  5. You can also use the INFORMATION_SCHEMA:

    DECLARE @column_name SYSNAME;

    SELECT ROUTINE_TYPE, ROUTINE_SCHEMA, ROUTINE_NAME
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_DEFINITION LIKE ‘%’ + @column_name + ‘%’
    UNION
    SELECT ‘VIEW’, TABLE_SCHEMA, TABLE_NAME
    FROM INFORMATION_SCHEMA.VIEWS
    WHERE VIEW_DEFINITION LIKE ‘%’ + @column_name + ‘%’

    Like

    • Hi Jerry,

      yes you can do this. The corresponding type for triggers is TR:

      SELECT obj.Name SPName, sc.TEXT SPText
      FROM sys.syscomments sc
      INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID
      WHERE sc.TEXT LIKE ‘%’ + ‘Name Your Column Here’ + ‘%’
      AND TYPE = ‘TR’

      Like

  6. Pingback: SQL SERVER – Find Column Used in Stored Procedure – Search Stored Procedure for Column Name – Part 2 « SQL Server Journey with SQL Authority

  7. Pingback: Dataset.ReadXML or ‘O Schema, Where Art Thou’

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

  9. Hello, everybody, I am looking for those procedures, triggers or any other objects in the database that use the operator *= into their statments
    I am trying with these examples above, as a result only retrieves 4 coincidences, when in fact the script must to return more that 4 coincidences. I am running this script under SQL 2012. Do I need other kind of script to retrieve those coincidences?

    Thanks in advance!

    Like

  10. Hello All. I have been looking on the web and this posting was “sort of” along the lines of what I was looking for. We are still on SQL 2000. Fingers crossed we will be on 2012 shortly. Of course we now have a request to “secure” a certain field. The field is a key field used in many stored procedures, some of which produce reports. It has been requested that only certain people be able to see the data in that field. Is there a way to setup something that would only allow a certain role or privilege to see the data in that column even if its in a stored procedure?

    Any thoughts would be appreciated.

    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