list User Stored Procedures that reference a specified table

  • Is there a way to list User Stored Procedures that reference a specified table?

  • Try sp_depends 'table name'.

    This system sp queries sysdepends (system table).

  • Thanks a lot!!!  That worked great!!!

  • Be careful though.  Sysdepends is not guaranteed to be accurate at all times.  The most thorough way is to grep through syscomments with a LIKE clause of  '%tablename%' and join that output to sysobjects to get the sproc name.  This can have issues also since columns can wrap in the middle of the table name so you need to concatenate them together for each sproc and then check it to be 100% certain you have found all sprocs correctly.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • As a follow-up to Kevin's post, this is something I did recently to provide a search facility where I work.

    I've got a sproc that creates a table containing all the relevant syscomments/sysobjects info, which is then searchable.  I can pass it on if anyone is interested; PM me if so.

  • not difficult - I usually drop this proc into master - can use it in from any database, will search for one or two strings

    Create procedure dbo.sp_FindText

    @wot varchar(250)=' ',

    @wot2 varchar(250)=' '

    -- ============================================================================

    --  Stored Procedure:  sp_FindText                         

    --                      

    --  Written by:  Colin Leversuch-Roberts

    --   kelem consulting limited

    --    http://www.kelemconsulting.co.uk

    --   (c) august 2004                         

    --                                                               

    --  Purpose:            Search for system objects containing the passed string(s)

    --   These are wild card searches

    --   Returns the object code and type of object                            

    --                                                               

    --  System:  master database

    --   does not need to be marked as a system object

    --

    --  Input Paramters:    @wot  varchar  Search string

    --    @wot2  varchar  Search string

    --                      

    --  Output Parameters:  None                                     

    --                                                               

    --  Usage:              Call from user database to be searched                                         

    --        EXEC dbo.sp_findtext 'container'

    --   EXEC dbo.sp_findtext 'rates','uat'

    --                                                               

    --  Calls:   nothing

    --  Uses:  syscomments, sysobjects   

    --                                                    

    --  Data Modifications: None                            

    --                                                               

    --  VERSION HISTORY

    --  Version No  Date   Description

    --  1   22-August-2004  Initial Release

    -- ============================================================================

    as

    set nocount on

    --

    select obj.name,obj.xtype,

    case obj.xtype

     when 'TR' then 'Trigger'

     when 'P' then 'Procedure'

     when 'V' then 'View'

     when 'TF' then 'Function'

     when 'IF' then 'Function'

     when 'FN' then 'Function'

     else 'Unknown'

    end

    ,c.text

    from dbo.syscomments c join dbo.sysobjects obj

    on obj.id=c.id

    where

    patindex(<A href="mailto:'%'+@wot+'%',text)0">'%'+@wot+'%',text)<>0 and patindex(<A href="mailto:'%'+@wot2+'%',text)0">'%'+@wot2+'%',text)<>0

    GO

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • Colin, your query does exactly what I cautioned against.  Suppose a line of a sproc wraps in syscomments (nvarchar 8000 IIRC), and it wraps in such a way to split a table name.  Example:

    select field1, field2 from tblmys

    plittable where a = @a

     

    Now your query will NOT identify that sproc as referencing tblmysplittable.  You really do need to concatenate the rows together (or at least each row to the next row) to guarantee finding all words correctly.

     

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Well, if you're running SQL Server 2005, you can query sys.sql_modules instead.  Something like the code below works well, because sys.sql_modules is varchar(max).

    -- This query returns the Name of the object, and it's entire definition

    select syso.[name], sysm.definition

    from sys.sysobjects syso

          join sys.sql_modules sysm on syso.id = sysm.[object_id]

    where sysm.definition like '%Table_Name%'

          and syso.uid = 1 -- only get objects owned by DBO (avoid system objects)

  • It's a simple script, never had any problems, but that's probably down to documentation and I usually try to avoid large procs as they rarely optimise correctly in cache. If I needed a more complex script I'd write one, I forget how many years I've used this script, at least since 7.0 and probably in 6.0 - yup I do agree about splits - you can always profile of course, or script out all your procs and search the resulting script file

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • Yeah, the likelyhood of specific search string spanning multiple blocks isn't high, but it can happen.  Syscomments.text is nvarchar(4000), so it's easy to span multiple segments.  We use Nice-Large-Comment-Blocks at the start of all database objects, and include history as the object changes.  It's easy to pass 4000 characters that way.  Check your objects, I expect you'll get more results than you thought:

    select id, count(*)
    from sys.syscomments
    group by id
    having count(*) > 1

     

    Dragged from the recesses of my time-addled brain... Here's a search script for SQL Server 2000 that will find any table reference, even spanning across multiple chunks.  Its only limitation is the 8000 char variable limit, so you're limited to searching a max of (8000 / (len(@sText) * 2)) chunks.  (ie: if you're searching for a string that's 500 characters long, you can't search past the 8th chunk, or 32,000th character.)

     

    SET ANSI_WARNINGS OFF

    SET NOCOUNT ON

    create table #results (objid bigint, [type] sysname, obj sysname, comment varchar(8000))

    declare @obj sysname,

     @type sysname,

     @objid bigint,

     @sText varchar(1027),

     @wrapText varchar(8000)

    -- Search for the following string

    set @sText = 'DeliveryDrop_Type'

    -- Look in syscomments for the search string

    insert into #results

    select syso.[id] as objid, substring(val.name, 5, 16) as [type], syso.[name] as [obj], [text]

    from syscomments sysc

     join sysobjects syso on sysc.[id] = syso.[id]

     join master.dbo.spt_values val on syso.xtype = substring(val.[name],1,2)

    where syso.[type] in ('FN', 'IF', 'P', 'RF', 'TF', 'TR', 'V')

     and [text] like '%' + @sText + '%'

    -- Collect the details of any objects that haven't yet matched the search string

    select syso.[id] as objid, syso.[name], substring(val.name, 5, 16) as [type]

    into #objects

    from sysobjects syso

     join master.dbo.spt_values val on syso.xtype = substring(val.[name],1,2)

     left join #results r on syso.[id] = r.objid

    where r.objid is null

     and syso.[type] in ('FN', 'IF', 'P', 'RF', 'TF', 'TR', 'V')

    while exists (select top 1 * from #objects)

    begin

     select top 1

      @obj = [name],

      @type = [type],

      @objid = [objid],

      @wrapText = ''

     from #objects

     select @wrapText = isNull(@wrapText, '')  + left(text, len(@sText) - 1) + '~' + right(text, len(@sText) - 1)

     from sys.syscomments

     where id = @objid

     if (CHARINDEX(@sText, @wrapText) > 0)

      insert into #results

      select @objid, @type, @obj, @wrapText

     delete from #objects

     where [objid] = @objid

    end

    select *

    from #results

    order by [type], [obj], [comment]

    drop table #objects

    drop table #results

    SET ANSI_WARNINGS ON

    SET NOCOUNT OFF

  •  

    ive used this in the past; it's a cursor, and resource intensive; it takes a couple of minutes to run on my 100meg database with 100's of procs and 1400+tables:

    sample results:

    ProcNameMightReferenceObject
    PR_ACTDELFAVORITESGMACT
    PR_ACTDELFAVORITESGMACTFAVORITES
    PR_COMPLETEDELETE_ACTGMACTFAVORITES
    PR_COMPLETEDELETE_ACTGMHOPWA6
    PR_COMPLETEDELETE_ACTGMHOPWA7

    --search all procedures and find out if the name of any of the objects appear in their text:

    SET NOCOUNT ON

    declare

     @isql varchar(2000),

     @objname varchar(64)

     --All User Tables, Views, Procedures, Table function or Scalar Function.

            Create Table #MightReference(

                            ProcName varchar(60),

                            MightReferenceObject varchar(60)

                         )

     declare c1 cursor for select name from sysobjects where xtype in ('U','V','P','TF','FN')

     open c1

     fetch next from c1 into @objname

     While @@fetch_status <> -1

      begin

                    --desired logic: if an object name exists in the syscomments of a procedure,

                    --it MIGHT mean the procedure references the object

                    --insert the two into a table for further evaulation.

                     Insert into #MightReference(ProcName,MightReferenceObject)

         select object_name(id),@objname from syscomments

                                where id in(select id from sysobjects where xtype='P' and name NOT like 'dt_%') 

                                and text like '%' + @objname + '%' 

      fetch next from c1 into @objname

      end

     close c1

     deallocate c1

            --avoid self referencing procs. "CREATE PROC MYPROC" statement returns itself!

            Select * from #MightReference

            where ProcName <> MightReferenceObject

            order by ProcName 

            Select * from #MightReference

            where ProcName <> MightReferenceObject

            order by MightReferenceObject

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Rick,

    Thanks for the script.

    However, when I ran it, I got the following errors:

    Warning: The table '#results' has been created but its maximum row size (8547) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

    Warning: The table '#results' has been created but its maximum row size (8547) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

    Server: Msg 8648, Level 17, State 95, Line 16

    Could not insert a row larger than the page size into a hash table. Resubmit the query with the ROBUST PLAN hint.

    Warning: The table '#results' has been created but its maximum row size (8547) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

    any ideas?

     

    Thank you

  • Lowell,

    Does your script solve the problem of stored procedure text spanning over multiple lines?

    If so, could you please point to the part of the code that takes care of that.

     

    Thank you

  • sql_er no it does not address the issue where a tablename might span across  the 4000 char mark in syscomments;

    in my case, i check sysdepends and this, and it's "good enough for government work", but not foolproof. I only have a few procs or functions that are big enough to span multi rows, so in my case it's not enough to worry about.

    Sergy has posted some code on this same subject that does consider multi rows, so if you need something bullet proof, search the  forums here; i've seen it several times, but couldn't find it in a search for this post.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Here's a simple script we use that does two joins to syscomments to search stored procedure text that spans two rows:

    Greg

    select distinct so.name from  sysobjects so

     join syscomments sc on sc.id = so.id

     left outer join  syscomments sc1 on  sc1.id = sc.id and  sc1.colid = sc.colid + 1

      where  so.type = 'p'

        and  ( sc.text like '%string%'

         or right(sc.text,500) + left(isnull(sc1.text,''),500) like '%string%'

       )

      order by so.name

     

    Greg

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply