by

SQL SERVER – Find Stored Procedure Related to Table in Database – Search in All Stored Procedure

Following code will help to find all the Stored Procedures (SP) which are related to one or more specific tables. sp_help and sp_depends does not always return accurate results.

----Option 1
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%tablename%'
----Option 2
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tablename%'

Reference : Pinal Dave (http://www.SQLAuthority.com)

About these ads

144 thoughts on “SQL SERVER – Find Stored Procedure Related to Table in Database – Search in All Stored Procedure”

    • Hello,

      I want to inquire that is it possible to write an SQL query in which I enter the stored procedure name and it returns me the package

      Reply

    • sp_depends does not return the same results in my experience.

      When I run sp_depends TABLE_NAME, I get a single result – a view. When I run either of the queries above, I get 13 results. 11 of those 13 are not what I am looking for (in addition to the table TABLE_NAME, there is a table called EX_TABLE_NAME), but there are 2 valid results – a view and a stored procedure.

      sp_help returns 0 results.

      Reply

    • Tried the first SQL Script given. Not sure how much it makes sence. Using ‘LIKE’ means you’ll get way lot more results than you need. And, removing ‘LIKE’ and using ‘=’ apparently doesn’t give any o/p.

      sp_depends on the other hand gives just the results that you need.

      Reply

  8. hi i want a write a querry in stored procedure which can help me for taking the value from user and serch the table and give the result and i like it also in trigers like above criteria so plz help me
    i am waiting for u

    Reply

  9. Hi Pinal,
    Excellent Query. Thanks for sharing. Would you please help me how i can find out Dependable Tables/Views/Stored Procedures/ Constraints/Indexes/ForeignKeys constraint for given Table ?

    Reply

  12. hi pinal
    i have a query realted with how to use stored procedure from java to insering or deleting or updating the value in the table. and also want to knw how to call them
    plz reply me soon.

    Reply

  13. Want to rename a table in a database.
    Looking for a T-SQL script that will update all stored procedures and views in a database with the new table name.

    Regards
    Kevin

    Reply

  14. Hi Pinal

    You might want to move this to another thread, but here’s an extended version of your example. I needed a way to search all objects in a database containing a certain string, in my case a reference to another database. This example makes it possible to find all references in triggers and stored procedures etc:

    SELECT DISTINCT o.name AS ObjectName,
    CASE o.xtype
    WHEN ‘C’ THEN ‘CHECK constraint’
    WHEN ‘D’ THEN ‘Default or DEFAULT constraint’
    WHEN ‘F’ THEN ‘FOREIGN KEY constraint’
    WHEN ‘FN’ THEN ‘Scalar function’
    WHEN ‘IF’ THEN ‘In-lined table-function’
    WHEN ‘K’ THEN ‘PRIMARY KEY or UNIQUE constraint’
    WHEN ‘L’ THEN ‘Log’
    WHEN ‘P’ THEN ‘Stored procedure’
    WHEN ‘R’ THEN ‘Rule’
    WHEN ‘RF’ THEN ‘Replication filter stored procedure’
    WHEN ‘S’ THEN ‘System table’
    WHEN ‘TF’ THEN ‘Table function’
    WHEN ‘TR’ THEN ‘Trigger’
    WHEN ‘U’ THEN ‘User table’
    WHEN ‘V’ THEN ‘View’
    WHEN ‘X’ THEN ‘Extended stored procedure’
    ELSE o.xtype
    END AS ObjectType,
    ISNULL( p.Name, ‘[db]‘) AS Location
    FROM syscomments c
    INNER JOIN sysobjects o ON c.id=o.id
    LEFT JOIN sysobjects p ON o.Parent_obj=p.id
    WHERE c.text LIKE ‘%code/SQL command/any text to search for%’
    ORDER BY Location, ObjectName

    Note: this CANNOT be used to find information in tables – that’s something competely different.

    Reply

    • A note of caution:
      This WON’T find occurrances in encrypted objects. Nearly got myself into troubles there…

      Here is how you check if you have any encrypted objects in your db:

      SELECT DISTINCT(c.id), name, type
      FROM syscomments c
      INNER JOIN sysobjects o ON o.id=c.id
      WHERE c.encrypted = 1

      Reply

      • In addition to my first post:

        It is also nice to be able to search occurance in SQL Agent Jobs

        USE msdb

        SELECT command, subsystem, database_name, last_run_date, last_run_time, step_name, step_id, job_id, step_uid
        FROM sysjobsteps WITH (NOLOCK)
        WHERE command like @search_string

        Reply

        • This is better

          use msdb

          DECLARE @search_string VARCHAR(255)
          SET @search_string = ‘%belagg%’

          SELECT sj.enabled AS jobEnabled,
          command, step_id AS jobStep, step_name,
          sj.name AS jobName, sj.description AS jobDescr,
          subsystem, database_name,
          last_run_date, last_run_time,
          sjs.job_id, step_uid
          FROM sysjobsteps sjs WITH (NOLOCK)
          INNER JOIN sysjobs sj ON sj.job_id = sjs.job_id
          WHERE command like @search_string

          Reply

    • i have 10 insert comand in stored procedure and i want to get in which table name and colunm name the error is coming

      Reply

  20. Pingback: SQL SERVER - 2005 2000 - Search String in Stored Procedure Journey to SQL Authority with Pinal Dave

  21. Nice website, Pinal.

    Using SSMS, You can also right click a table and select “View Dependencies” to see which objects depend on the table including stored procedures.

    Reply

  26. i need to find a particular column used in diff stored proc in sql server 2005 database.how will i find all the stored proc names where i am using that particular column.

    Reply

    • Try this

      select routine_name from INFORMATION_SCHEMA.ROUTINES
      where ROUTINE_TYPE=’PROCEDURE’
      and OBJECT_DEFINITION(object_id(‘routine_name’)) like ‘% col_name %’

      Reply

      • I tryed this q. but it always gives me an empty result.
        Need to find all columns in database where the column Createddate has ben used.

        Reply

        • @Elena,

          Can you please re-phrase your question. Do you want to get all Table / View information where the column createdDate has been used ?

          – To get list of tables that has CreateDate column.

          Select Table_Name, Column_Name
          From Information_Schema.columns
          Where Column_Name = ‘CreatedDate’

          ~Peace

          Reply

  30. Hi Pinal,

    This query is great!

    Was wondering can it be taken one step further and be used to rename a table in the DB.

    eg. change table ‘Blah’ to ‘BlahBlahBlah’

    Where this would update throughout the DB. Then i won’t have to manually change every object.

    Thanks

    Reply

    • declare @sql varchar(max)
      set @sql=”
      select @sql=@sql+’exec sp_rename ”’+table_name+”’,”’+table_name+’blah;”’ from INFORMATION_SCHEMA.TABLES
      where TABLE_TYPE=’BASE TABLE’
      EXEC(@sql)

      Reply

  31. Great article, thanks! Just a quick extension of the example to tables referenced by all procedures and views follows

    SET NOCOUNT ON

    IF OBJECT_ID(‘tempdb..#work’) IS NOT NULL
    DROP TABLE #work

    CREATE TABLE #work
    ( name sysname
    ,xtype char(2)
    ,tablename sysname )

    – List objects dependent on tables

    DECLARE cur_tables CURSOR
    READ_ONLY
    FOR
    SELECT name
    FROM sysobjects
    WHERE xtype = ‘u’
    AND left(name,2) ‘dt’

    DECLARE @name sysname
    OPEN cur_tables

    FETCH NEXT FROM cur_tables INTO @name
    WHILE (@@fetch_status -1)
    BEGIN
    IF (@@fetch_status -2)
    BEGIN
    INSERT INTO #work
    SELECT DISTINCT o.name ,o.xtype, @name as tablename
    FROM syscomments c
    INNER JOIN sysobjects o ON c.id=o.id
    WHERE c.TEXT LIKE ‘%’ + @name + ‘%’
    END
    FETCH NEXT FROM cur_tables INTO @name
    END

    CLOSE cur_tables
    DEALLOCATE cur_tables

    select *
    from #work
    order by name

    Reply

  34. Hi Pinal,
    This is the first time i’m writing to u and lemme tell you that
    you have done an incredible job.
    Thanx for the website and yeah nice query man.

    Reply

  35. Can you please tell me, What will i do if i have to view th e constraints for a particular table.
    Like I have add primary key constraint in a table.
    Now if i want to check for that PK_Constraint what will be the query that I have to execute.
    Please help.

    Reply

  36. Dear Pinal

    The query for finding the dependencies of a table is based on the syscomments table and the like operator , so it is giving results if the dbobject is in comments section or in a part of a sentence, which is inaccurate .

    Can you give me a query for this problem.

    Thank You.
    Dileep.

    Reply

  37. Is there any system sp in sql server to get the create, insert or select script of a table instead of right clicking the table anme and selecting the particular query type(Create to, insert to or select to) as selecting from the menu takes long tme to fetch the query?

    Reply

  38. Would you please help me how i can find out Dependencies of views on stored procedure?
    following query returns the dependencies of table but does not return the dependencies of views?

    select distinct referenced_major_id from sys.sql_dependencies where
    object_id = {0} //table object id

    Reply

  39. Hi Friends,
    I need your help, to get the dependency table list of the stored procedure.

    USE dbA
    Create Procedure usp_Test
    As
    BEGIN
    If exists( select * from tblA)
    SELECT * FROM tblB
    END

    When I execute, sp_depends usp_Test
    It displays the tblB and tblA table lists

    But when I call other database table names inside my procedure, it’s not showing the results, instead of it throws the error
    ‘Object does not reference any object, and no objects reference it.’

    USE dbA
    Create Procedure usp_Test
    As
    BEGIN
    If exists( select * from dbB..tblC)
    SELECT * FROM dbB..tblD
    END
    Could you please help me to get these tables (tblC and tblD)

    I am using SQL Server 2005 not 2008 :-(

    Manesh P

    Reply

  40. Hi, We will have upgradation from SQL 2000 to SQL 2005. During running of upgrade advisior I got this messgae :: “Outer join operators *= and =* are not supported in 90 compatibility mode”. I was wonder will it be possible for us to find this kind of query. Pinal your query really works but I can not search for views. So will you help me out?

    Thanks
    Sneha

    Reply

  41. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  48. Hello Pinal,

    Great Piece of work.

    How can get the printed version of the query if we are using “sp_helptext” ?

    Looking forward to your sincere reply for the same.

    With
    Best Regards

    Pallav Vidwans

    Reply

  49. –usp_dts_find_object_in_routine
    –object contains table, view,stored procedure and user defined function)
    create procedure usp_dts_find_object_in_routine
    (
    @routine_name nvarchar(200)=null
    )
    as

    –temp variables for retrieving data from cursor
    declare @temp_routine_name nvarchar(200)
    declare @temp_routine_type nvarchar(200)
    declare @temp_routine_definition nvarchar(4000)
    declare @temp_created datetime
    declare @temp_last_altered datetime

    declare @temp_referred_routine_name nvarchar(200)

    declare @temp_table_name nvarchar(200)
    –declare @temp_table_type nvarchar(20)

    –create routine container
    if(object_id(‘temp..#temp_routines’) is null)
    begin
    create table #temp_routines
    (
    id int identity(1,1) primary key,
    routine_name nvarchar(200),
    referred_table nvarchar(4000),
    referred_routine nvarchar(4000),
    –table_type nvaarchar(20),
    routine_type nvarchar(20) null,
    routine_definition nvarchar(4000) null,
    created datetime null,
    last_altered datetime null
    )
    end
    else
    begin
    truncate table #temp_routines
    end

    –declare cursor for routines

    declare cur_routine_list cursor for
    select routine_name,routine_type,routine_definition,created,last_altered
    from information_schema.routines
    where routine_name=case when @routine_name is null or ltrim(rtrim(@routine_name))=” then routine_name else @routine_name end
    order by routine_type

    open cur_routine_list
    –find tables which is referred in routines
    fetch next from cur_routine_list into @temp_routine_name,@temp_routine_type,@temp_routine_definition,@temp_created,@temp_last_altered
    while(@@fetch_status=0)
    begin
    if (not exists(select * from #temp_routines where routine_name=@temp_routine_name))
    begin
    insert into #temp_routines(routine_name,routine_type,routine_definition,created,last_altered)
    values(@temp_routine_name,@temp_routine_type,@temp_routine_definition,@temp_created,@temp_last_altered)
    end
    else
    begin
    update #temp_routines
    set routine_name=@temp_routine_name,
    routine_type=@temp_routine_type,
    routine_definition=@temp_routine_definition,
    created=@temp_created,
    last_altered=@temp_last_altered
    where routine_name=@temp_routine_name
    end
    –loop table list
    declare cur_table_list cursor for
    select table_name from information_schema.tables

    open cur_table_list
    fetch next from cur_table_list into @temp_table_name
    while(@@fetch_status=0)
    begin
    if(charindex(@temp_table_name,@temp_routine_definition)>0)
    begin
    update #temp_routines
    set referred_table=isnull(referred_table+’,',”)+@temp_table_name
    where routine_name=@temp_routine_name
    end
    fetch next from cur_table_list into @temp_table_name
    end
    close cur_table_list
    deallocate cur_table_list

    –loop referred routine list
    declare cur_referred_routine_list cursor for
    select routine_name
    from information_schema.routines
    order by routine_type

    open cur_referred_routine_list
    fetch next from cur_referred_routine_list into @temp_referred_routine_name
    while(@@fetch_status=0)
    begin
    if(charindex(@temp_referred_routine_name,@temp_routine_definition)>0 and @temp_referred_routine_name@temp_routine_name)
    begin
    update #temp_routines
    set referred_routine=isnull(referred_routine+’,',”)+@temp_referred_routine_name
    where routine_name=@temp_routine_name
    end
    fetch next from cur_referred_routine_list into @temp_referred_routine_name
    end
    close cur_referred_routine_list
    deallocate cur_referred_routine_list

    fetch next from cur_routine_list into @temp_routine_name,@temp_routine_type,@temp_routine_definition,@temp_created,@temp_last_altered
    end

    close cur_routine_list
    deallocate cur_routine_list

    select *
    from #temp_routines
    order by last_altered,routine_name

    Reply

  53. sir,

    there is one table(M_GeneralLedger) in my system.based on that table i wrote the two stored procedures for assets and liabilities.and i created one more dump table.how to send the data to dump table from those two stored procedures…

    Reply

  54. there is one tables(M_GeneralLedger,M_PrimaryGroup,M_GroupLedger) in my system.based on that tables i wrote the two stored procedures for assets and liabilities one procedure for assets and another procedure for liabilities. i created one more dump table.how to send the assets and liabilities data to dump table from those two stored procedures..any one can help to me plz

    Reply

      • hi,
        hope the above query gives errors as incorrect syntax error near union.
        you have to write down two queries instead of one query to insert the data from two SP. say,

        insert into dump_table
        exec SP1

        insert into dump_table
        exec SP2

        note: make sure there is no mismatch of datatype while inserting.
        as the data type of the columns of the SP result set should match the data type of the table columns.

        Reply

        • My point is directly use the queries used in the procedure than executing and copying the results to the table. Thats why I specified it as “Query used in procedure 1″

          Reply

  56. This is actually wrong, this query returns all database objects (not just stored procedures) and will return any database object that contains the string. The string could be in a comment or it could be a substring in another table name. The potential returning incorrect results is quite high.

    Reply

  57. can i generate table script using query analyzer or stored procedure,we right click and select generate script at table object can we do the same using any sp

    Reply

  58. Hi All ,

    I am new to the SP.
    My question is how to debug the SP,if it is a 1000 of query inside?
    and one more question- if the SP contains any DML statements, how to debug?
    Request you to reply me ASAP?

    Thanks

    Reply

  59. Hi All,

    I need to change a table column data type. Prior to that, I would like to get all the dependencies on this column like references,key,indexes,constraints etc.Also, column references in stored procedures,views etc.

    Is there a easy way to do it in SQL server 2008?

    Thanks

    Reply

  60. Hi Pinal,

    I read all the comments and reply
    I have a question.

    I successfully executed the Stored Procedure using .net

    We have a website and 80 unique customer ID

    Can you please tell me how can I call stored procedure to these 80 unique ID and how to recognize parameters

    I appreciate your help and support

    Thanks
    Ankit

    Reply

  63. SELECT DISTINCT o.name, o.xtype
    FROM syscomments c
    INNER JOIN sysobjects o ON c.id=o.id
    WHERE c.TEXT LIKE ‘%tablename%’ –-it will not return correct data if table name is from key word

    EXEC sp_depends tablename –This will return the correct data

    Reply

  64. Hi, I would like to find all the tables a particular stored procedure is using. Those tables may be from different databases in server.

    Reply

  65. How can i check if a row exists in a table using stored procedure. If a row does exist then how will i update the table

    Reply

  68. Hi Pinal,

    I have a question,

    I have 50 instances in my production environment and i have a common database in all the instances.

    My question is how can i find what are all the differences in the databases across all the instances like (tables, store Procedures, keys, constraints, functions…..etc).

    How do i find the difference? Is there any script to find the differences.

    Thanks,
    Rahul.

    Reply

  69. Hi,
    I have to go through about 1000 procedures and verify input parameters and the resultset and wonder if it possíble to query som magic table containing result sets for stored procedures. Input parameters are no problem but I have problems with getting the result set for each procedure. My plan is to create 2 databases (containing the versions I should compare) and run som sort of scripts identifying procedures that differs.

    Reply

  71. hi, I am the beginner in sql server. I want to create Update Stored Procedure and how can i update one column in the table which contains multiple columns

    Reply

  72. Hi Dave

    Here is my Q?

    I have a stored Procedure called ‘xyz’. I want a query to display the name of the database containing that ‘xyz’ stored procedure.

    Thanks a lot.

    Reply

  73. Hi Pinal,

    I am facing a strange issue. On one of my tables data gets deleted automatically. Is there a way to find out which jobs are affecting the table?

    Regards,
    Priya

    Reply

  74. 1. create a function that generates random alphanumeric code e.g ‘NFG6Y5′. the function should accept a number
    specifying size of characters in the code.

    2. create a table to store the codes and the code column should be unique.

    3. create a stored proc that uses the function created to get codes and inserts them in the table created in the
    above step. The stored proc should accept number of codes to generate, and size of each code.

    using aspx c#,

    1. create an application that allows users to log in, and using previous stored proc, allows user to view codes and
    generate more.

    Reply

  76. Wow, this is something I’d always thought would be extremely useful at times, but I hadn’t had any idea that there was a query to get this information immediately. Works great. Thanks!

    Reply

  80. Is it possible to use this query in a variable and then EXEC that variable like the following?

    DECLARE @tableName NVARCHAR (800)
    DECLARE @dbName NVARCHAR(800)
    DECLARE @sql NVARCHAR(800)
    SET @dbName = ‘TempDB’
    SET @tableName = ‘Table1′
    SET @sql = ‘SELECT DISTINCT o.name, o.xtype FROM syscomments c INNER JOIN sysobjects o ON c.id = o.id WHERE c.TEXT LIKE “%@tableName%”‘
    EXEC (@sql)

    Reply

    • Yes use like below

      SET @sql = ‘SELECT DISTINCT o.name, o.xtype FROM syscomments c INNER JOIN sysobjects o ON c.id = o.id WHERE c.TEXT LIKE “%’+@tableName+’%”‘

      Reply

  84. —-Option 2
    SELECT DISTINCT o.name, o.xtype
    FROM syscomments c
    INNER JOIN sysobjects o ON c.id=o.id
    WHERE c.TEXT LIKE ‘%tablename%’

    will return all database objects which contains text ‘tablename’ that means if user adds comment which has the word ‘tablename’ but not depend upon the table ‘tablename’ will be returned which is not desired result, isn’t it?.

    Reply

  89. Hello,

    I am looking for the following information, can some one help.

    Looking for help, I am looking for SQL script which will provide me the
    • store procedure name
    • Schedule of this procedure
    • Last Run time
    • Run by
    • Table Name/Database against that procedure is running.

    Reply

  93. hi pinal i m ur fan….for dot net N ms sql ser
    i m having problem how can return two table in stored procedure in mysql
    and how can hold those two table in java seprately IN JAVA

    Reply

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

  96. Hi
    does stored procedure execute only one select query

    create procedure proc_purchase_det(@p_id int)
    AS
    Begin
    select SUM(TotalCost)as ‘Month Total’ from PurchaseItemDetails where
    TotalCost > 25000
    select * from PurchaseItemDetails where purchaseid = @p_id
    END

    i m getting error for this SP
    please clarify me

    Reply

  98. create procedure proc_purchase_det(@p_id int)
    AS
    Begin
    select SUM(TotalCost)as [Month Total] from PurchaseItemDetails
    where TotalCost > 25000
    select * from PurchaseItemDetails where purchaseid = @p_id
    END

    Reply

  102. Hello,
    I wan to get particular record from database.
    suppose i have 1000 rows and i want to get record from 75 to 100 than what i have to do…

    Reply

    • To my knowledge there is no built in function in mssql. A script could do this, but it would be too extensive to paste and explain it in a comment like this. Maybe Pinal can write an article and show us an example.

      Before you go further with encryption, I would like to give you our reason why we DEcrypted our sprocs.

      First the good sides of encryption:
      * you want to hide what the sprocs are doing
      * you want to minimize the risk of someone changing sprocs on the fly

      Now to the not so good sides:
      * It gives you a false impression of security. We had encrypted sprocs, developed by a former employee, that we couldn’t understand what they were doing so we decrypted them (yes, you can decrypt encrypted sprocs)
      * you can’t see what is going on inside a sproc because the code is hidden!

      Read the the bad sides again, especially the last one. Weigh the (partly false) advantages of encryption and the disadvantage and soon you end up understanding it is better to restrict databases access and implement a professional version handling process of sprocs so you are able to “roll back” to the version that once was accepted, before that creative developer did her/his “on the fly”-hack which messed up everything.

      Reply

  107. Pingback: SQL SERVER – Beginning New Weekly Series – Memory Lane – #002 « SQL Server Journey with SQL Authority

  108. Pingback: SQL SERVER – Weekly Series – Memory Lane – #007 « SQL Server Journey with SQL Authority

  109. Pingback: SQL SERVER – Weekly Series – Memory Lane – #007 « SQL Server Journey with SQL Authority

Leave a Reply

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

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

Cancel

Connecting to %s