String-search on objects in a specified database

,

Procedure 'p_find_string' allows users to specify any database on a server in searching for a specified character string.  Supported for string-searching:  table columns, view columns, trigger, function, and procedure code.  Produces a report showing: object type, owner, object name, column id (for tables and views) or what line number (for triggers, functions, and procedures), and the name and definition (for tables and views) or the line of code (for triggers, functions, and procedures).

Example:  Search the pubs database for all columns and lines of code containing the string 'author':

EXEC p_find_string @p_string = 'author'
                 , @p_dbname = 'pubs'

Pre-requisites:  Functions 'f_contains' and 'f_delimited', and stored procedure 'p_get_column_attribute', which I previously submitted.

IF EXISTS (SELECT 1
           FROM   sysobjects
           WHERE  xtype = 'P'
             AND  name = 'p_find_string_in_objects')
  DROP PROCEDURE p_find_string
GO
CREATE PROCEDURE p_find_string
                   @p_string VARCHAR (4000)
                 , @p_dbname SYSNAME
                 , @p_debug  CHAR (1) = NULL
/*  
||======================================================================  
|| Date created:  7/15/2002 (Dise)
||
|| Purpose:  This procedure searches database objects for the
||           presence of a character string, and reports where the
||           string is to be found.
|| 
|| Parameters:
||   @p_string -- string for which to search
||   @p_dbname -- which database to run from
||   @p_debug  -- a non-NULL value turns on certain runtime displays
||======================================================================
*/AS
  DECLARE @TRUE            BIT
        , @FALSE           BIT
        , @done            BIT
        , @colid           INT
        , @len             INT
        , @linenumber      INT
        , @pos             INT
        , @retc            INT
        , @seq             INT
        , @cr              CHAR (1)
        , @lf              CHAR (1)
        , @crlf            CHAR (2)
        , @lfcr            CHAR (2)
        , @q               CHAR (1)
        , @coldef          VARCHAR (100)
        , @delim           VARCHAR (2)
        , @linetext        VARCHAR (4000)
        , @msg             VARCHAR (8000)
        , @objecttype      VARCHAR (9)
        , @otext           VARCHAR (4000)
        , @sqlcmd          VARCHAR (8000)
        , @str             VARCHAR (4000)
        , @dbname          SYSNAME
        , @objectname      SYSNAME
        , @owner           SYSNAME

  /*
  ||====================================================================
  || Initialize...
  ||====================================================================
  */  SELECT @retc  = 0
       , @TRUE  = 1
       , @FALSE = 0
       , @cr    = CHAR (13) -- carriage return
       , @lf    = CHAR (10) -- line feed
       , @q     = CHAR (39) -- single quote
  SELECT @crlf  = @cr + @lf
       , @lfcr  = @lf + @cr
  /*
  ||====================================================================
  || Ensure that the specified database exists on the server...
  ||====================================================================
  */  IF @p_dbname IS NULL
--THEN
    SELECT @dbname = DB_NAME ()
  ELSE
    IF EXISTS (SELECT 1
               FROM   master..sysdatabases
               WHERE  name = @p_dbname)
  --THEN
      SELECT @dbname = @p_dbname
    ELSE
      BEGIN
        SELECT @msg = ' database by name of '       + @p_dbname
                    + ' does not exists on server ' + @@SERVERNAME
        GOTO PRC_ERROR
      END
  --END IF
--END IF

  /*
  ||====================================================================
  || Input string must be non-NULL and not equal to blanks...
  ||====================================================================
  */  IF @p_string IS NULL
  OR @p_string = ''
--THEN
    BEGIN
      SELECT @msg = ' searched-for string "'      + @p_string + '"'
                  + ' must be non-NULL and non-blank'
      GOTO PRC_ERROR
    END
--END IF

  /*
  ||====================================================================
  || Main logic...
  ||====================================================================
  */  /*
  ||====================================================================
  || Create a table to hold database object info...
  ||====================================================================
  */  CREATE TABLE #objects
    (seq        INT IDENTITY
   , objecttype VARCHAR (9)
   , owner      VARCHAR (60)
   , objectname VARCHAR (60)
   , colid      INT
   , otext      VARCHAR (4000))

  /*
  ||====================================================================
  || Create a table to hold the number of carriage-return characters
  || contained in each row of syscomments for stored procedures, for
  || figuring out linenumbers within the procedure...
  ||====================================================================
  */  CREATE TABLE #crcounts
    (colid      INT
   , crcount    INT)

  /*
  ||====================================================================
  || Create a table to hold the report...
  ||====================================================================
  */  CREATE TABLE #report
    (seq                    INT IDENTITY
   , objecttype             VARCHAR (9)
   , owner                  VARCHAR (60)
   , objectname             VARCHAR (60)
   , columnid_or_linenumber INT
   , columndef_or_linetext  VARCHAR (4000))

  /*
  ||====================================================================
  || Search table and view definitions for column names matching the
  || searched-for string...
  ||====================================================================
  */  SELECT @sqlcmd =
       'SELECT CASE o.xtype'
    +          ' WHEN ' + @q + 'U'     + @q
    +          ' THEN ' + @q + 'TABLE' + @q
    +          ' ELSE ' + @q + 'VIEW'  + @q
    +        ' END'
    +        ', u.name'
    +        ', o.name'
    +        ', c.colid'
    +        ', c.name'
    +  ' FROM '   + @dbname + '..syscolumns c'
    +    ' JOIN ' + @dbname + '..systypes   t ON c.xtype = t.xtype'
    +    ' JOIN ' + @dbname + '..sysobjects o ON c.id    = o.id'
    +    ' JOIN ' + @dbname + '..sysusers   u ON o.uid   = u.uid'
    +  ' WHERE  o.xtype IN (' + @q + 'U' + @q
    +                    ', ' + @q + 'V' + @q + ')'
    +    ' AND  c.name LIKE ' + @q + '%' + @p_string + '%' + @q

  IF @p_debug IS NOT NULL
--THEN
    PRINT @sqlcmd
--END IF

  INSERT INTO #objects (objecttype
                      , owner
                      , objectname
                      , colid
                      , otext)
    EXEC (@sqlcmd)

  /*
  ||====================================================================
  || Search procedure and function definitions for column names matching
  || the searched-for string...
  ||====================================================================
  */  SELECT @sqlcmd =
       'SELECT CASE o.xtype'
    +          ' WHEN ' + @q + 'P'         + @q
    +          ' THEN ' + @q + 'PROCEDURE' + @q
    +          ' ELSE ' + @q + 'FUNCTION'  + @q
    +        ' END'
    +       ', u.name'
    +       ', o.name'
    +       ', c.colid'
    +       ', c.text'
    + ' FROM '    + @dbname + '..syscomments c'
    +    ' JOIN ' + @dbname + '..sysobjects  o ON c.id    = o.id'
    +    ' JOIN ' + @dbname + '..sysusers    u ON o.uid   = u.uid'
    + ' WHERE c.id IN (SELECT c2.id'
    +                ' FROM ' + @dbname + '..syscomments c2'
    +                ' WHERE  c2.text LIKE '
    +                    @q + '%' + @p_string + '%' + @q + ')'
    +   ' AND o.xtype IN (' + @q + 'FN' + @q
    +                  ', ' + @q + 'P'  + @q
    +                  ', ' + @q + 'TF' + @q
    +                  ', ' + @q + 'TR' + @q + ')'

  IF @p_debug IS NOT NULL
--THEN
    PRINT @sqlcmd
--END IF

  INSERT INTO #objects (objecttype
                      , owner
                      , objectname
                      , colid
                      , otext)
     EXEC (@sqlcmd)

  /*
  ||====================================================================
  || In preparation for using SEQ as a retrieval key, create an index
  || on it...
  ||====================================================================
  */  CREATE UNIQUE CLUSTERED INDEX objidx ON #objects (seq)

  /*
  ||====================================================================
  || Process the list of objects...
  ||
  || Rather than use a CURSOR, we're going to use the SEQ column
  || as a key...
  ||====================================================================
  */  SELECT @seq  = 0
       , @done = @FALSE
  
  WHILE @done = @FALSE
  BEGIN
    /*
    ||==================================================================
    || Get the next row of data from #objects, if one exists.  If not,
    || set a flag that causes falling out of the loop...
    ||==================================================================
    */    IF EXISTS (SELECT 1
               FROM  #objects
               WHERE  seq > @seq)
  --THEN
      SELECT @seq        = seq
           , @objecttype = objecttype
           , @owner      = owner
           , @objectname = objectname
           , @colid      = colid
           , @otext      = otext
      FROM   #objects
      WHERE  seq = (SELECT MIN (seq)
                    FROM  #objects
                    WHERE  seq > @seq)
    ELSE
      BEGIN
        SELECT @done = @TRUE

        GOTO ENDOFLOOP
      END
  --END IF

    /*
    ||==================================================================
    || If the objects is a table or view, then the only additional
    || information needed is the column definition, which is acquired
    || thru the procedure 'p_get_column_attribute'...
    ||==================================================================
    */    IF @objecttype IN ('TABLE', 'VIEW')
  --THEN
      BEGIN
        EXEC p_get_column_attribute
               @p_which_attrib = 'TYPEDEF'
             , @p_tablename    = @objectname
             , @p_columnname   = @otext
             , @p_out_msg      = @msg    OUTPUT
             , @p_out_attrib   = @coldef OUTPUT
             , @p_dbname       = @dbname
             , @p_owner        = @owner

        /*
        ||==============================================================
        || All accumulated info about the table or view now goes into
        || the #report table...
        ||==============================================================
        */        INSERT INTO #report (objecttype
                           , owner
                           , objectname
                           , columnid_or_linenumber
                           , columndef_or_linetext)
          VALUES (@objecttype
                , @owner
                , @objectname
                , @colid
                , @otext + ' ' + @coldef)
      END

    /*
    ||==================================================================
    || If the objects is a function or a procedure, then the needed
    || information includes the linenumber and the text of the line
    || in which the string occurs...
    ||==================================================================
    */    ELSE IF @objecttype IN ('FUNCTION', 'PROCEDURE', 'TRIGGER')
  --THEN
      BEGIN
        /*
        ||==============================================================
        || We want to allow for any possible combination of carriage
        || return or line feeds, changing them all to simple carriage
        || returns...
        ||==============================================================
        */        SELECT @otext = REPLACE (@otext, @crlf, @cr)
        SELECT @otext = REPLACE (@otext, @lfcr, @cr)
        SELECT @otext = REPLACE (@otext, @lf,   @cr)

        /*
        ||==============================================================
        || Some procedures and functions require more than one row in
        || 'syscomments' to be stored in.  By keeping a count of the
        || number of carriage-return values in each 'syscomments' row,
        || we can compute the number of lines up to any point in the
        || module...
        ||==============================================================
        */        IF @colid = 1
      --THEN
          BEGIN
            TRUNCATE TABLE #crcounts

            INSERT INTO #crcounts (colid, crcount)
              VALUES (0, 0)
          END
      --END IF

        INSERT INTO #crcounts (colid
                             , crcount)
          SELECT @colid
               , dbo.f_contains (@cr, @otext)

        /*
        ||==============================================================
        || We have retrieved all rows in 'syscomments' for any procedure
        || or function containing the string; therefore, not every row
        || retrieved may have the string inside of it.  (However, even
        || so, we may need to know how many lines of code are contained
        || in this row, so it gets processed anyway.)
        ||
        || The following returns the address of the string inside of
        || the current 'syscomments.text' column, if the string exists
        || in it, and also the length of the text...
        ||==============================================================
        */        SELECT @pos = CHARINDEX (@p_string, @otext)
             , @len = LEN (@otext)

        /*
        ||==============================================================
        || The following 'inner' loop is designed to examine the
        || 'syscomments.text' value to find occurrences of the string.
        || There may be one or many such occurrences; if there were
        || zero, the looping criteria are not met (i.e., @pos = 0)...
        ||==============================================================
        */        WHILE @pos >  0
          AND @pos <= @len
        BEGIN
          /*
          ||============================================================
          || The part of the text occurring before the string starts
          || needs to be examined for carriage-return characters, which
          || indicate what line number the string occurs within.  The
          || 'f_contains' function counts how many such
          || characters are within this segment, which the 'f_delimited'
          || function returns the text of this line...
          ||============================================================
          */          IF @pos = 1
        --THEN
            SELECT @str = ''
          ELSE
            SELECT @str = SUBSTRING (@otext, 1, @pos - 1)
        --END IF
          
          SELECT @linetext   = dbo.f_delimited (@otext, @cr, @pos)
               , @linenumber = dbo.f_contains (@cr, @str)
                             + SUM (crcount)
                             + 1
          FROM  #crcounts
          WHERE  colid < @colid

          /*
          ||============================================================
          || The assembled information is put into the #report table...
          ||============================================================
          */          INSERT INTO #report (objecttype
                             , owner
                             , objectname
                             , columnid_or_linenumber
                             , columndef_or_linetext)
            VALUES (@objecttype
                  , @owner
                  , @objectname
                  , @linenumber
                  , @linetext)

          /*
          ||============================================================
          || We want to skip past other occurrences of the string
          || within the current line of procedure/function code, since
          || this line has already been reported on, but still want
          || to pick up other occurrences on subsequent lines...
          ||============================================================
          */          IF @pos < @len
        --THEN
            BEGIN
              SELECT @pos = CHARINDEX (@cr, @otext, @pos)

              IF  @pos > 0
              AND @pos < @len
            --THEN
                SELECT @pos = CHARINDEX (@p_string, @otext, @pos + 1)
              ELSE
                SELECT @pos = 0
            --END IF
            END
          ELSE
            SELECT @pos = 0
        --END IF

        /*
        ||==============================================================
        || End of 'inner' loop...
        ||==============================================================
        */        END

        /*
        ||==============================================================
        || If the next row in #objects is a continuation of the current
        || procedure or function, the number of lines already counted
        || up until now needs to be saved to preserve accuracy...
        ||==============================================================
        */        SELECT @linenumber = @linenumber
                           + dbo.f_contains (@cr, @otext)
      END
  --END IF

    /*
    ||==================================================================
    || When no more rows exist in the #objects table, processing falls
    || out of the loop here...
    ||==================================================================
    */ENDOFLOOP:
  END

  /*
  ||====================================================================
  || Final query...
  ||====================================================================
  */  /*
  ||====================================================================
  || Print the banner...
  ||====================================================================
  */  PRINT 'Executing procedure ' + OBJECT_NAME (@@PROCID) + '...'
  PRINT 'Searching for string: '    + @p_string
  PRINT REPLICATE ('-', 72)

  SELECT objecttype
       , owner
       , objectname
       , columnid_or_linenumber
       , columndef_or_linetext
  FROM #report
  ORDER BY objecttype
         , owner
         , objectname
         , columnid_or_linenumber

  /*
  ||====================================================================
  || When all has gone well, step around the error trap...
  ||====================================================================
  */  GOTO PRC_EXIT

PRC_ERROR:
  /*
  ||====================================================================
  || Error trap...
  ||====================================================================
  */  SELECT @retc = 1

  RAISERROR (@msg, 16, 1)

PRC_EXIT:
  /*
  ||====================================================================
  || Exit the procedure...
  ||====================================================================
  */  RETURN @retc
GO

Rate

Share

Share

Rate