Sponsored By Aspose - File Format APIs for .NET

Aspose are the market leader of .NET APIs for file business formats – natively work with DOCX, XLSX, PPT, PDF, MSG, MPP, images formats and many more!

Sql dependency report from query analyzer

If you take a bunch of code from sp_help and kinda move it around and rearrange it, you can produce a pretty good little object dependency report. Just take this code, plop it right into query analyzer, select the database and hit F5.  Viola, dependency report.  Looks better if you hit CTRL-T first so you get it in text format and not columns.  Some portions, such as line the ‘query’ column manipulation in the first select statement, should be a UDF, but then I’d have to provide code for both and this is just easier.  Sorry about not coloring it in HTML. Also, this is not optimized for speed so don’t expect it to run to quickly. You would have to replace the temp tables with normal tables, run the query to build the tables (against a small database), then hit CTRL-L to get the execution plan and look at where you will want to create your stats and build your indexes.  Guage it against the master database, which running this against the master database takes 31 seconds on the machine I have Sql installed on.  I tested it against a single proc P4 1.8 with 256 RAM.

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

This entry was posted in Sql Development. Bookmark the permalink. Follow any comments here with the RSS feed for this post.

11 Responses to Sql dependency report from query analyzer

  1. Rob says:

    I have not inspected the output yet, but so far it seems very impresive!

  2. Leha says:

    Awesome!!!!! Great, fantastic !!!!!!!

  3. Nirav Patel says:

    Hi Raymond,

    Fantastic piece of work man. Saves me a lot of money compared to buying a third party tool (being a small freelance developer).

    Would you mind if I use this code for private and non-commercial use with my own little modifications?

    Cheers,
    Nirav

  4. Satish Panwar says:

    Nice rearrenging of code. Really help when we modify a particular object in sql server, now we can know what will require changes , instead of getting bugs after we have made changes. Wonderful work done. Thanks Buddy…

  5. Copy to Word first, then to your SQL editor. Preserves line-breaks.

  6. That is odd. That is something that w.bloggar does automatically I think, even though I put <pre> around the code.

  7. I’m not certain why this is happening, but I changed your code’s html <br> tags to <p> tags and now it copies fine.

  8. I’m not having that problem, it comes out fine in query analyzer to me. If anybody wants the file, just let me know by posting a comment here with your email address and I’ll send you the .sql file with this code.

  9. When I copy and paste the code, it all comes out on one line. This happens whether I go straight into QueryAnalyzer or Notepad. This is a nice piece of code, I’m going through it to get it running… Just thought I’d let you know.

    -Steve

Leave a Reply