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

SQL SERVER - Find Column Used in Stored Procedure - Search Stored Procedure for Column Name  manager-and-employee 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.

SQL SERVER - Find Column Used in Stored Procedure - Search Stored Procedure for Column Name  resultfindcolumninsp

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 (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – Example of Width Sensitive and Width Insensitive Collation
Next Post
SQL SERVER – 2012 Functions – FORMAT() and CONCAT() – An Interesting Usage

Related Posts

27 Comments. Leave new

  • Chalapathi Pinisetty
    July 15, 2012 7:25 am

    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:

    Thanks

    Reply
  • Reply
  • Imran Mohammed
    July 15, 2012 8:22 am

    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.

    Reply
    • Excellent Imran – loved it!

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

      Reply
  • Dattatrey Sindol (Datta)
    July 15, 2012 11:49 am

    But since “sys.syscomments” is marked for removal in future , I think we should use “sys.sql_modules” or “information_schema.routines”. Thoughts ?

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

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

    Reply
  • Mathias Keip
    July 16, 2012 1:04 pm

    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 + ‘%’

    Reply
  • Hi Pinal,
    Can you do the same for triggers?
    Jerry

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

      Reply
  • what about sp_depends ‘stored_procedurename’

    Reply
  • how to add table new column name in insert_stored procedure

    Reply
  • Hi pinal i want to list out if the tabled used inside the sp then i want to list out it
    so search column like ‘tables’

    can we get this in sql?????

    Reply
  • How to get the list of all tables present in a particular(single) stored procedure ?

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

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

    Reply
  • Hi Sir,

    Thanks a lot but it is not quering the distinct records.

    SELECT DISTINCT OBJECT_NAME(OBJECT_ID),
    object_definition(OBJECT_ID)
    FROM sys.Procedures
    WHERE object_definition(OBJECT_ID) LIKE ‘%’ + ‘wip’ + ‘%’ AND TYPE = ‘P’
    order by OBJECT_NAME(OBJECT_ID)

    SELECT OBJECT_NAME(M.object_id), M.*
    FROM sys.sql_modules M
    JOIN sys.procedures P
    ON M.object_id = P.object_id
    WHERE M.definition LIKE ‘%wip%’

    SELECT DISTINCT obj.Name SPName
    FROM sys.syscomments sc
    INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID
    WHERE sc.TEXT LIKE ‘%’ + ‘wip’ + ‘%’
    AND TYPE = ‘P’ order by SPName

    Reply
  • This doesn’t work when the Stored Proc is in the Different Database and also table is in the different database.

    Reply
  • Can you tell me how to search all of the databases on a server for stored procedures containing a string? Unfortunately, we have columns that are passed around from db to db.

    I have this:

    DECLARE @command varchar(2048);

    SELECT @command = ‘USE ?
    SELECT obj.Name SPName
    , sc.TEXT SPText
    FROM sys.syscomments sc
    INNER
    JOIN sys.objects AS obj
    ON sc.Id = obj.OBJECT_ID
    WHERE sc.TEXT LIKE ‘ + char (39) + ‘%employee%id%’ + char(39) + ‘
    AND TYPE = ”P”

    EXEC sp_MSforeachdb @command

    But it doesn’t tell me which databases the stored procedures are in.

    –Stephen

    Reply

Leave a Reply

Menu