I felt a pang of conscience after having inflicted Obenglobish on an unsuspecting SQL Server fraternity. I decided therefore to provide something useful instead. Here is a way of seaching all your routines (stored procedures, triggers, functions and views) within a database to find every occurence of whatever text string you want to look for. Why is that useful when the information schema view ROUTINES allows you do just that, in a portable way?
Well, in versions of SQL Server prior to 2005, routines were stored in a whole lot of chunks of script in syscomments which then had to be assembled in the right order to get the entire source. In SQL Server 2000, the view only has the first 4000 characters, (the contents of the first chunk) which is hardly going to be sufficient. So then one rolls up ones sleeves and searches through syscomments? Bad idea, because the string you are searching for could span two chunks of your script. The solution must be to do what they originally wanted to do in version 6- put the source in TEXT.
Working with TEXT is a nightmare due to the restrictions around manipulating a text variable. However, with the restictions on the size of varchars in versions before 2005, there is not much else one can do.
To get a feel for the complexity of working with the source of a database at this level, it is instructive to look at the source of the ROUTINES view, or the source of sp_HelpText in SQL Server 2000. Ouch, it is not a pretty sight.
This routine was originally written so as to provide an easy way of searching the source of database routines for a specified string from a browser, but I've since found it useful for all sorts of other tasks. CREATE PROCEDURE spWhereIsItUsed
/* This Procedure is used in development work. It is for
SQL Server 2000 only. It is all so much simpler in 2005.
It searches through the code in the database for whatever string
you care to specify and displays the name of each routine
that the string is in, and its context (up to 255 characters
around it) of EVERY occurence so you can see, for example,
whereabouts an object is being called. It is not really the
same as having the build script in the Query Analyser. This
procedure makes it a lot quicker to find a problem.
Obviously, the code can be hacked for a particular problem,
as you end up with a table of all the routines in the database
with the routine name and all the text.
*/
@SearchString VARCHAR(40),--the string you want to search for
@BackSpan INT=21,--when you find a string,
--how many characters back you show
@ForwardSpan INT= 40--when you find a string,
--how many characters forward you show
--e.g:
-- spWhereIsItUsed 'raiserror'
-- spWhereIsItUsed 'textptr',100,100
-- spWhereIsItUsed 'blog[sg]',100,100 --find blogg or blogs
-- spWhereIsItUsed 'b_gg',100,100 --find begg, bigg, etc
AS
DECLARE @ii INT
DECLARE @iiMax INT
DECLARE @ColID INT
DECLARE @objectID INT
DECLARE @currentProcedure INT
DECLARE @pointerValue varbinary(16)
DECLARE @EndOfText INT
DECLARE @Chunk NVARCHAR(4000)
DECLARE @pos INT
DECLARE @size INT
DECLARE @WhereWeAre INT--index into string so far
DECLARE @context INT
IF @BackSpan + @ForwardSpan > 255
BEGIN
RAISERROR (' sorry but your context span is just too great',16,1)
RETURN 1
END
SET nocount ON
DECLARE @raw_text TABLE --create a table so we can iterate through it
--a row at a time in the correct order
(
ourID INT IDENTITY(1,1), --
colid INT, [id] INT,
chunk NVARCHAR(4000)
)
--now get all the code routines into the table
INSERT INTO @Raw_Text (colid, id, Chunk)
SELECT colid, id, text
FROM syscomments s
WHERE OBJECTPROPERTY(id, 'IsExecuted') = 1
--view, procedure, function, or trigger
AND encrypted=0
ORDER BY id, colid
--now we create the table of all the routines with their
--text source in the correct order.
CREATE TABLE #routine
(
ourID INT IDENTITY(1,1),
objectName VARCHAR(80),
Definition text
)
-- start the loop, adding all the nvarchar(4000) chunks
SELECT @ii=MIN(ourID), @iiMax=MAX(ourID)
FROM @Raw_Text
WHILE @ii<=@iiMax
BEGIN
SELECT
@colid=colid,
@objectID=id,
@chunk=chunk
FROM @Raw_Text WHERE ourID=@ii--no sir. No cursors
IF @Colid=1
BEGIN
INSERT INTO #Routine (Objectname, Definition)
SELECT OBJECT_NAME(@objectID), @chunk
-- get the pointer for the current procedure name / colid
SELECT @currentProcedure=@@Identity
SELECT @pointerValue = TEXTPTR(Definition)
FROM #Routine
WHERE ourid=@currentProcedure
END
ELSE
BEGIN
-- find out where to append the #temp table's value
SELECT @EndOfText = DATALENGTH(Definition)
FROM #Routine
WHERE ourid=@currentProcedure
--Take a deep breath. We are dealing with text here
UPDATETEXT #Routine.definition
@pointerValue @EndOfText 0 @chunk
END
SELECT @ii=@ii+1
END
--select objectname,datalength(definition) from #routine
DECLARE @results TABLE (
ourID INT IDENTITY(1,1),
ObjectName VARCHAR(40),
offset INT,
context VARCHAR(255))
SELECT @ii=MIN(ourID), @iiMax=MAX(ourID) FROM #Routine
WHILE @ii<=@iiMax--avoid cursors. Do we look like amateurs?
BEGIN --for each routine...
SELECT @pos=1,
@size=DATALENGTH(definition),
@WhereWeAre=1
FROM #Routine WHERE ourID=@ii
--find all occurences of the string in the current text
WHILE @WhereWeAre<@size
BEGIN
SELECT @pos=PATINDEX('%'+@SearchString+'%',
SUBSTRING(definition,@whereWeAre,8000))
FROM #Routine WHERE ourID=@ii
IF @pos>0
BEGIN
SELECT @context=CASE
WHEN @whereWeAre+@pos-@backspan<=1
THEN 1
ELSE @whereWeAre+@pos-@backspan END
INSERT INTO @results (ObjectName, offset, Context)
SELECT ObjectName, @whereWeAre+@pos,
SUBSTRING(definition,@context,@BackSpan+@ForwardSpan)
FROM #Routine
WHERE ourID=@ii
SELECT @WhereWeAre=@WhereWeAre+@pos
END
ELSE SELECT @WhereWeAre=@WhereWeAre+6000
END
SELECT @ii=@ii+1
END
SELECT ObjectName, offset, [context]='...'+context+'...'
FROM @results