Knowing What to Test When Changing a SQL Server Object

We were changing our linked server and needed to test every page in our website that used that link server.  None of the pages used it directly, but they did call stored procs that would use it or stored procs that would in turn call other stored procs that us it.    I need to find all of the pages that eventually would use it so that we could test them all.  The fact that it could be nested within stored proc calls made it a little more difficult.   Here is how I did it:

Step 1:  A simple stored proc used for searching for text within other stored procs. 

This work for SQL Server 2005

create proc dbo.Find(@search VARCHAR(100) = '') AS  
begin  
set nocount on  
  
SET @search = '%' + @search + '%'  
  
select object_name(o.object_id) Object, o.type
from sys.sql_modules m  
join sys.objects o on m.object_id = o.object_id  
where m.definition like @search  
order by 1  
  
end  

Step 2: Make a working table to store you work as you process the stored procs.

create table spDoc
(parentObj varchar(500),
obj varchar(500),
[type] varchar(3),
level int)

You will be storing the object name (stored proc, function, or trigger) in the column “obj”.  You will be storing the calling stored proc in the “parentObj” column. 

Step 3: Create a stored proc to process your search.

create proc doc(@obj varchar(256))
as
begin
set nocount on
declare @level int


if exists (select 1 from spDoc where parentObj = @obj)
    return  -- already done.

select @level = isnull(level,0)+1
from spDoc
where obj = @obj

if @level is null
    set @level = 1


--Find all of the objects that have the text of the current object.
insert into spDoc
(obj, [type])
exec Find @obj

-- The @obj is the parent of the rows just created.
update spDoc
set parentObj = @obj,
level = @level
where parentObj is null

--Delete where they are both the same.
delete from spDoc
where parentObj = @obj
and obj = @obj


--Loop thru each of the rows just created
--and recursively call this function.
DECLARE @currentObj varchar(256)

DECLARE @getObjs CURSOR

SET @getObjs = CURSOR FOR
select obj
from spDoc
where parentObj = @obj


OPEN @getObjs

FETCH NEXT
FROM @getObjs INTO @currentObj

WHILE @@FETCH_STATUS = 0
BEGIN
    exec doc @currentObj  -- recursive call

    FETCH NEXT
    FROM @getObjs INTO @currentObj
END

CLOSE @getObjs
DEALLOCATE @getObjs

end

If you were trying to document the object called “MyLink” you would call it like:

   doc MyLink

Step 4: Get your results.

Once the processing is done you only need a listing of the parent objects and this is the list that you would use to check your front end code.  

select distinct parentObj
from spDoc
order by 1

And that is all there is to it.