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.

In one of the systems I work on we have the problem of needing to track the different timezones that our users are in.  In doing this we need to account for daylight savings time.
I see code like this used in many places:

select   ...
dbo.fn_convertFromUTC(@StartUTC, @timeZone) as startLocal,
dbo.fn_convertFromUTC(@EndUTC, @timeZone) as endLocal
from      ...

and thought this might have an impact on performance since the conversion function will fire twice for each row in the result set.  I came up with a function that takes 3 parameters: @start, @end (both in UTC time) and @timeZone and returns a table like the one below.  You would then joint to this table to do the conversion.  This will allow for the same functionality, but will only be fired once or twice per result set.


startTime          endTime   offset  isHr2
3/1/07 7:00     3/11/07 6:59    -5    0
3/11/07 7:00    11/4/07 6:59    -4    0
11/4/07 7:00    11/4/07 7:59    -5    1
11/4/07 8:00    11/5/07 9:00    -5    0

--Here is some test code that shows how this would be used:

declare @startViewUTC DateTime, @endViewUTC DateTime,  @timeZone char(4)
set @startViewUTC = '3/1/2007 7:00am'
set @endViewUTC = '11/5/2007 9:00am'
set @timeZone = 'EPT'  -- Eastern Prevailing Time


select ai.startTime,
ai.endTime,
dateadd(hh, tzStart.offset,ai.startTime) as startLocal,
dateadd(hh, tzEnd.offset,ai.endTime) as endLocal,
tzStart.isHr2, tzStart.offset, tzEnd.isHr2, tzEnd.offset
from      dbo.fn_getAllIntervals(@startViewUTC, @endViewUTC, 3) ai
inner join dbo.fn_getTimeZoneOffsets(@startViewUTC, @endViewUTC, @timeZone)  tzStart
on ai.startTime between tzStart.startTime and tzStart.endTime
inner join dbo.fn_getTimeZoneOffsets(@startViewUTC, @endViewUTC, @timeZone)  tzEnd
on ai.endTime between tzEnd.startTime and tzEnd.endTime


--And here is the functions that are used to return the time zone offset table:

-- First we need to have a function that returns the Daylight Saving Start DateTime for a given year.

CREATE ALTER  FUNCTION dbo.fn_getDSTStart (
    @year int
)
RETURNS datetime
AS
BEGIN
    DECLARE @dstStart datetime

    IF @year < 2007
        SET @dstStart = DateAdd(mi, 120, DateAdd(dd, (8 - DatePart(dw, Cast(@year AS varchar) + '0401')) % 7, Cast(@year AS varchar) + '0401'))
    ELSE
        SET @dstStart = DateAdd(mi, 120, DateAdd(dd, (8 - DatePart(dw, Cast(@year AS varchar) + '0308')) % 7,Cast(@year AS varchar) + '0308'))

    RETURN @dstStart
END

CREATE FUNCTION dbo.fn_getDSTEnd (
    @year int
)
RETURNS datetime
AS
BEGIN
    DECLARE @dstEnd datetime

    IF @year < 2007
        SET @dstEnd = DateAdd(mi, 120, DateAdd(dd, 1 - DatePart(dw, Cast(@year AS varchar) + '1031'), Cast(@year AS varchar) + '1031'))
    ELSE
        SET @dstEnd = DateAdd(mi, 120, DateAdd(dd, (8 - DatePart(dw, Cast(@year AS varchar) + '1101')) % 7,Cast(@year AS varchar) + '1101'))

    RETURN @dstEnd
END

CREATE       FUNCTION dbo.fn_getTimeZoneOffsets (
 @start datetime,
 @end datetime,
 @timeZone varchar(4)
)
RETURNS @timeZoneOffsets TABLE
 (startTime datetime,
  endTime datetime,
  offset int,
   isHr2 bit)
AS
BEGIN
declare @defaultOffset int

  --obtain the Standard Time, GMT Offset (this is not a complete list of timezones.)
  Select @defaultOffset =
        case @timeZone
      when 'GMT' then 0
      when 'AST' then -4
      when 'ADT' then -3
      when 'EST' then -5
      when 'EDT' then -4
      when 'CST' then -6
      when 'CDT' then -5
      when 'MST' then -7
      when 'MDT' then -6
      when 'PST' then -8
      when 'PDT' then -7
      when 'AKST' then -9
      when 'AKDT' then -8
      when 'EPT' then -5
      when 'CPT' then -6
      when 'MPT' then -7
      when 'PPT' then -8
        else 0
        end
 
 IF @timeZone IN ('CPT', 'EPT', 'MPT', 'PPT')
  BEGIN
  DECLARE @offsetTime int,
    @dstStart datetime,
    @dstEnd datetime,
    @yearStart datetime,
    @yearEnd datetime,
    @prevYearEnd datetime,
    @startYear int,
    @endYear int,
    @Year int
    
  set @startYear = year(@start)
  set @endYear = year(@end)
  set @Year = @startYear
  -- keep in mind these dates are all in GMT.
  --break the year into each part that has a different value
  while @Year <= @endYear
  begin
   set @dstStart = dateadd(hh,@defaultOffset*-1,dbo.fn_getDSTStart(@Year))
   set @dstEnd = dateadd(hh,@defaultOffset*-1,dbo.fn_getDSTEnd(@Year))
   set @yearStart = cast('01/01/'+cast(@Year as char(4)) as DateTime)

   set @yearEnd = cast('12/31/'+cast(@Year as char(4))+' 23:59:59.000' as DateTime)
   set @yearStart = case when @yearStart < @start then @start else @yearStart end
   set @yearEnd = case when @yearEnd < @end then @yearEnd else @end end


    if(@prevYearEnd is null)
    begin
       insert into @timeZoneOffsets
       Select @yearStart, dateadd(ss,-1,@dstStart), @defaultOffset, 0 isHr2
    end
    else
    begin
      update @timeZoneOffsets
       set endTime=dateadd(ss,-1,@dstStart)
       where endTime=@prevYearEnd
    end

   set @prevYearEnd = @yearEnd

   insert into @timeZoneOffsets
   Select @dstStart, dateadd(ss,-1,@dstEnd), @defaultOffset + 1, 0 isHr2
 
   insert into @timeZoneOffsets
   Select @dstEnd, dateadd(ss,-1,dateadd(hh,1,@dstEnd)), @defaultOffset, 1 isHr2
 
   insert into @timeZoneOffsets
   Select dateadd(hh,1,@dstEnd), @yearEnd, @defaultOffset, 0 isHr2
 
   set @Year = @Year + 1
 
  end

  delete from @timeZoneOffsets
  where endTime < @start
  or startTime > @end

    update @timeZoneOffsets
    set startTime = @start
    where startTime = (select min(startTime) from @timeZoneOffsets)

    update @timeZoneOffsets
    set endTime = @end
    where endTime = (select max(endTime) from @timeZoneOffsets)

 
   END
 ELSE
  insert into @timeZoneOffsets     
  Select @start, @end, @defaultOffset, 0 isHr2

RETURN
END

GO