Sql dependency report from query analyzer

set nocount on

select ‘name’ = (o1.name),
 ’id’ = (o1.id),
    ‘type’ = substring(v2.name, 5, 16),
   ’query’ =
  replace(replace(
  substring(c7.,charindex(‘@’,left(c7.,charindex(‘AS’+Char(13)+Char(10),replace(Upper(c7.),’AS ‘+Char(13)+Char(10),’AS’+Char(13)+Char(10)),1)),1),
  charindex(‘AS’+Char(13)+Char(10),replace(Upper(c7.),’AS ‘+Char(13)+Char(10),’AS’+Char(13)+Char(10)),1)-charindex(‘@’,left(c7.,charindex(‘AS’+Char(13)+Char(10),replace(Upper(c7.),’AS ‘+Char(13)+Char(10),’AS’+Char(13)+Char(10)),1)),1))
  ,’))’,')’)
  ,’nt])’,'nt]’)
  into [dbo].#Initial
  from  [dbo].sysobjects o1
   inner join master.dbo.spt_values v2 on o1.xtype = substring(v2.name,1,2) collate database_default and v2.type = ‘O9T’
   inner join [dbo].syscomments c7 on o1.id = c7.id and o1.xtype = ‘p’
  where  o1.xtype <> ‘s’
  and  o1.xtype <> ‘c’
  and  left(o1.name,3) <> ‘dt_’
  and  left(o1.name,2) <> ‘dd’
  order by ‘type’, ‘name’

update [dbo].#Initial set query = ” where charindex(‘CREATE’,query,1) > 0
update [dbo].#Initial set query = replace(query,Char(13)+Char(10),”)

while exists(select * from [dbo].#Initial where charindex(‘  ‘,query,1) > 0)
 update [dbo].#Initial set query = replace(query,’  ‘,’ ‘)

CREATE TABLE [dbo].[#Report] (
 [IDC] [int] IDENTITY (1,1) NOT NULL ,
 [objectid] [int] NULL ,
 [objectname] [nvarchar] (776) NULL ,
 [objecttype] [varchar] (100) NULL ,
 [depname] [varchar] (776) NULL ,
 [deptype] [varchar] (100) NULL ,
 [depupdated] [varchar] (10) NULL ,
 [depselected] [varchar] (10) NULL ,
 [depcolumn] [varchar] (100) NULL
) ON [PRIMARY]

declare @objid int   /* the id of the object we want */
declare @found_some bit   /* flag for dependencies found */
declare @dbname sysname

declare lcReport cursor LOCAL FAST_FORWARD for select ‘name’ = (o1.name),
    ‘type’ = substring(v2.name, 5, 16),
   ’oid’ = (o1.id)
  from  [dbo].sysobjects  o1
   ,master.dbo.spt_values v2
   ,[dbo].sysusers  s6
  where  o1.xtype = substring(v2.name,1,2) collate database_default and v2.type = ‘O9T’
  and  o1.uid = s6.uid
  and  left(o1.[name],3) <> ‘dt_’
  and  (o1.xtype = ‘p’
  or  o1.xtype = ‘u’
  or  o1.xtype = ‘v’
  or  o1.xtype = ‘fn’)
  order by ‘type’, ‘name’

open lcReport
declare @objname nvarchar(776)
declare @objtype varchar(50)
declare @oid int

fetch next from lcReport into @objname, @objtype, @oid

while @@fetch_status = 0
begin
 set @dbname = parsename(@objname,3)

 /*
 **  See if @objname exists.
 */
 select @objid = object_id(@objname)
 if @objid is not null
 BEGIN
  /*
  **  Initialize @found_some to indicate that we haven’t seen any dependencies.
  */
  set @found_some = 0

  insert [dbo].#Report select @oid, @objname, @objtype, ”, ”, ”, ”, ”
 
  /*
  **  Print out the particulars about the local dependencies.
  */
  if exists (select *
   from [dbo].sysdepends
    where id = @objid)
  begin
   insert [dbo].#Report select @oid, @objname, @objtype, ‘name’ = (o1.name),
     type = substring(v2.name, 5, 16),
     updated = substring(u4.name, 1, 7),
     selected = substring(w5.name, 1, 8),
                ‘column’ = ISNULL(col_name(d3.depid, d3.depnumber),”)
   from  [dbo].sysobjects  o1
    ,master.dbo.spt_values v2
    ,[dbo].sysdepends  d3
    ,master.dbo.spt_values u4
    ,master.dbo.spt_values w5 –11667
    ,[dbo].sysusers  s6
   where  o1.id = d3.depid
   and  o1.xtype = substring(v2.name,1,2) collate database_default and v2.type = ‘O9T’
   and  u4.type = ‘B’ and u4.number = d3.resultobj
   and  w5.type = ‘B’ and w5.number = d3.readobj|d3.selall
   and  d3.id = @objid
   and  o1.uid = s6.uid
   and deptype < 2
   order by ‘name’,'column’

   set @found_some = 1
  end

  /*
  **  Now check for things that depend on the object.
  */
  if exists (select *
   from [dbo].sysdepends
    where depid = @objid)
  begin
   insert [dbo].#Report select distinct @oid, @objname, @objtype, ‘name’ = (o.name)+ ‘ depends on’,
    type = substring(v.name, 5, 16),
    ”, ”, ”
    from .[dbo].sysobjects o, master.dbo.spt_values v, [dbo].sysdepends d,
     [dbo].sysusers s
    where o.id = d.id
     and o.xtype = substring(v.name,1,2) collate database_default and v.type = ‘O9T’
     and d.depid = @objid
     and o.uid = s.uid
     and deptype < 2

   set @found_some = 1
  end
 END

 fetch next from lcReport into @objname, @objtype, @oid
end

close lcReport
deallocate lcReport

CREATE CLUSTERED  INDEX [idx_TDR] ON [dbo].[#Report]([IDC]) ON [PRIMARY]

update [dbo].#Report set objectname = ”, objecttype = ” where depname <> ”

select IDC,
 ’Object’=case when objecttype <> ” then ‘Database Object ‘+objecttype+’ ‘+objectname
 when depcolumn <> ” And CharIndex(‘depends on’,depname,1) = 0 then ‘     Depends on ‘+deptype+’ ‘+depname + ‘ column ‘ + depcolumn
 when deptype <> ” And CharIndex(‘depends on’,depname,1) = 0 then ‘     Depends on ‘+deptype+’ ‘+depname
 when depcolumn <> ” then ‘     Required by ‘+deptype+’ ‘+replace(depname,’ depends on’,”) + ‘ column ‘ + depcolumn
 when deptype <> ” then ‘     Required by ‘+deptype+’ ‘+replace(depname,’ depends on’,”) end,
 ’Input’=ISNULL((select top 1 replace(replace(query,Char(9),’ ‘),’,@’,', @’) from [dbo].#Initial where [id] = [objectid] and objecttype <> ”),”)
 into [dbo].#Final
 from [dbo].#Report
 ORDER BY IDC ASC

select object,input from [dbo].#Final

drop table [dbo].#Report
drop table [dbo].#Initial
drop table [dbo].#Final

set nocount off
GO