how to find where a stored procedure is being used

Question

Using SQL Server 2000, is there a way to search for a pattern globally in all trigger procedures?

Where a stored procedure is being called is hiding from me.

This is my first post so be kind.

Sacramentarian

What type of pattern, please include an example. – Mountain

@AaronBertrand because OP might be expecting more extensive pattern matching such as those requiring a 'regex' like definition. – Mountain

@AaronBertrand sure, I've found it usually helps for the OP to provide an example to prevent spending time on an answer that doesn't really address the real question, but YMMV. – Mountain

Answer 1

This will search triggers, procedures, functions and views on SQL Server 2000 (recommend against this approach on newer versions; see this blog post for a much better way):

SELECT o.name
 FROM syscomments AS c
 INNER JOIN sysobjects AS o
 ON c.id = o.id
 WHERE c.text LIKE '%procedurename%';

Some danger here, of course:

1. syscomments will take procedures > 4000 rows and split them up into multiple rows. So there is a slight chance that a large procedure might only mention your search string on a boundary point, and not turn up at all. There is also a chance that such a procedure might show up in the list twice (you can add a GROUP BY to eliminate that).
2. Beware of false positives. Your search string could be included in a comment. Or if you have a stored procedure named GetAuthorSubscriptions and you're looking for %GetAuthors%, it will still show up. Using a case-sensitive search with a COLLATE clause may help, but not necessarily eliminate it.

More information here:

• How do I find a stored procedure containing <text>?

I highly recommend moving off of SQL Server 2000. If not for the 8 billion other benefits, this task is much easier in more modern versions.

Note that your stored procedure might not be getting called from within the database - it could be an ad hoc call from an app, someone's open copy of Management Studio, or even a job. To search jobs you can use:

SELECT 
  job_name = j.name, 
  s.step_name
FROM msdb.dbo.sysjobs AS j
INNER JOIN msdb.dbo.sysjobsteps AS s
ON j.job_id = s.job_id
WHERE s.command LIKE '%procedurename%';

Still hasn't turned it up? Run a server-side trace filtering on TextData LIKE '%procedurename%'...

Oenomel

Thanks Aaron, found it using your 1st select. – Sacramentarian

Answer 2

Using SQL Server 2017 Using SQL Server Management Studio

To view the dependencies of a procedure in Object Explorer

• In Object Explorer, connect to an instance of Database Engine and then expand that
• instance.
• Expand Databases, expand the database in which the procedure belongs, and then expand Programmability.
• Expand Stored Procedures, right-click the procedure and then click View Dependencies.
• View the list of objects that depend on the procedure.
• View the list of objects on which the procedure depends.
• Click OK.

Using Transact-SQL
you can follow the steps here

for more info check the original article at
https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/view-the-dependencies-of-a-stored-procedure?view=sql-server-2017

Henning

I see you are asking about SQL Server 2000 but just in case someone stumbled upon this question – Henning

Answer 3

Here is a procedure I have written which gets the source from the system tables, and puts it in a table line by line. This will make it easier to scan text and cater for strings that may be cut off in the system tables, due to wrapping text. if you run this daily or hourly etc, it will give you near real time results.

You can scan the v$source table to get a distinct list of procedure names, but thought would be useful to put whole solution here.

If there are any bugs let me know, as It hasn't been tested exhaustively.

CREATE TABLE [dbo].[v$source](
            [theDb] [varchar](100) NULL,
            [theLineNo] [int] NULL,
            [theName] [varchar](1000) NULL,
            [theText] [varchar](8000) NULL,
            [theType] [varchar](100) NULL,
            [theCreateDate] [datetime] NULL,
            [theOrderNum] [int] NULL,
            [DateCreated] [datetime] NULL,
            [DateUpdated] [datetime] NULL
        ) ON [PRIMARY]

    CREATE TABLE [dbo].[v$source_unsplit](
        [theDb] [varchar](100) NULL,
        [theName] [varchar](1000) NULL,
        [theText] [varchar](8000) NULL,
        [theType] [varchar](100) NULL,
        [theCreateDate] [datetime] NULL,
        [theOrderNum] [int] NULL,
        [DateCreated] [datetime] NULL,
        [DateUpdated] [datetime] NULL
    ) ON [PRIMARY]
    GO


    CREATE FUNCTION [dbo].[GetLHS]
    (
        @p_delim   varchar(1),
        @p_string  varchar(max)
    )
    RETURNS varchar(max)
    AS
    BEGIN
       declare @l_pos int;
       set @l_pos = charindex(@p_delim,@p_string,1);

       --if (@l_pos = 0)
       --   return @p_string;

       return substring(@p_string,1,iif(@l_pos=0,len(@p_string),@l_pos-1));

    END

    CREATE FUNCTION [dbo].[GetRHS]
    (
        @p_delim   varchar(1),
        @p_string  varchar(max)
    )
    RETURNS varchar(max)
    AS
    BEGIN
       declare @l_pos int;
       set @l_pos = charindex(@p_delim,@p_string,1);

       if (@l_pos = 0)
          return '';

       return substring(@p_string,@l_pos+1,len(@p_string));

    END



CREATE PROCEDURE sp_bld_v$source
    AS
    BEGIN
        delete from v$source_unsplit


        EXEC sp_MSforeachdb
        'USE ?; 
         insert into dict..v$source_unsplit (TheDB,TheName,TheText,TheType,TheCreateDate,TheOrderNum)
         SELECT ''?'' thedb, o.name, substring(c.Text,1,8000),xtype,crdate,colid
         FROM syscomments AS c
         INNER JOIN sysobjects AS o
         ON c.id = o.id'

        delete from v$source

        DECLARE @C_TEXT_CURSOR as CURSOR;
        DECLARE @l_thedb       varchar(1000)
        DECLARE @l_thename     varchar(1000)
        DECLARE @l_theordernum int
        DECLARE @l_text        varchar(max)
        DECLARE @l_lhs         varchar(max) = 'NULL'
        DECLARE @l_line_no     int
        DECLARE @l_createDate  datetime
        declare @l_thetype     varchar(10)

        SET @C_TEXT_CURSOR = CURSOR FOR
         select   theDb,TheName,theOrderNum,thetext,theCreateDate,thetype--,replace(theText,char(10),'@^@') 
         from     v$source_unsplit
         order by theDb,TheName,theOrderNum

        OPEN @C_TEXT_CURSOR;
        FETCH NEXT FROM @C_TEXT_CURSOR INTO @l_thedb,@l_thename,@l_theordernum,@l_text,@l_createDate,@l_thetype

        WHILE @@FETCH_STATUS = 0
        BEGIN
           if @l_theordernum = 1 
           begin
              set @l_line_no = 1
              if @l_lhs <> 'NULL'
              begin
                  insert into v$source (TheDB,TheLineNo,TheName,TheText,TheType,TheCreateDate,TheOrderNum)
                  select @l_thedb,@l_line_no,@l_thename,@l_lhs,@l_thetype,@l_createDate,@l_theordernum
              end
           end
           else
           begin
              set @l_text = @l_lhs+@l_text -- reconstuct previous line
           end

           while charindex(char(10),@l_text)>0 
           begin
              set @l_lhs  = lib.dbo.GetLHS(char(10),@l_text)
              set @l_text = lib.dbo.GetRHS(char(10),@l_text)

              insert into v$source (TheDB,TheLineNo,TheName,TheText,TheType,TheCreateDate,TheOrderNum)
              select @l_thedb,@l_line_no,@l_thename,@l_lhs,@l_thetype,@l_createDate,@l_theordernum

              --print(@l_text)
              set @l_line_no = @l_line_no+1


           end
           set @l_lhs = @l_text

           FETCH NEXT FROM @C_TEXT_CURSOR INTO @l_thedb,@l_thename,@l_theordernum,@l_text,@l_createDate,@l_thetype
        END

        CLOSE @C_TEXT_CURSOR;
        DEALLOCATE @C_TEXT_CURSOR;

    END
    GO

Whitworth