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 
