Count number of tables referenced in a query / view

• Bill Geake

  Ten Centuries

  Points: 1242

  More actions

  June 8, 2006 at 2:44 am

  #190822

  I've hit the 256/260 limit in a view which does many unions of queries on other views which do many unions, udfs that query other views, etc. May sound like bad design but it's closely based on the business process and is easy to read. The code is not finished and I have another business area to deal with, so my number of table references is going to rise.

  I have a few ideas for getting round this (temp tables, some more efficient coding around commonalities or near-commonalities) but really need a quick method of counting my references. Is there something that can do this for me?

  Thanks,

  Bill.

  PS this system was meant to be in UAT 6 weeks ago... so I'm in a hurry.

• stax68

  SSChampion

  Points: 11711

  More actions

  June 8, 2006 at 10:22 am

  #642588

  --draft code - test well.

  -- view name is 'testdeps' - change to the name of a user view  - it should work to a depth of 31. 

   

  create

  function getdeps(@objid int, @level int = 0, @parentid int = null)

  returns

  @retval table(level int, name sysname, id int, parentid int, xtype varchar(2))

  as

  begin

  if @level > 30 return

  declare @name sysname, @xtype varchar(2), @rowcnt int, @key int, @i int

  declare @children table(rowid int identity, childid int)

  select @name = o.name, @xtype = o.xtype, @i = 1

  from sysobjects o where o.id = @objid and o.xtype in ('U','V')

  select @rowcnt = @@rowcount

  if @rowcnt = 0 return

  insert @retval values(@level, @name, @objid, @parentid, @xtype)

  if @xtype = 'V'

  begin

  insert @children(childid) select distinct o.id from sysobjects o join sysdepends d on o.id = d.depid

  where o.xtype in ('U','V') and d.id = @objid

  select @rowcnt = @@rowcount

  if @rowcnt = 0 return

  while @i <= @rowcnt

  begin

  select @key = childid from @children where rowid = @i

  insert @retval

  select * from dbo.getdeps(@key, @level+1, @objid)

  select @i = @i + 1

  end

  end

  return

  end

  go

  declare

  @id int

  select @id = object_id('testdeps')

  select

  * from dbo.getdeps(@id,0,null)

  go

  drop

  function getdeps

  Tim Wilkinson

  "If it doesn't work in practice, you're using the wrong theory"
  - Immanuel Kant

• stax68

  SSChampion

  Points: 11711

  More actions

  June 8, 2006 at 12:24 pm

  #642642

  If you have a query to test, make it into a view, or if necessary a stored proc. The code can be amended to cover sps, but sysdepends is not always reliable for sps because of deferred name resolution: an sp can be created even though it uses an invalid object name. but if this happens, you will get an explicit warning to that effect. so you could try scripting all your objects (schema only!) into an empty db and checking taht no such warnings arise, You should then be OK.

  Two other things - 1. dynamic SQL is more tricky to track, though you could use a showplan or profiler output to see the tables referenced when it runs; and 2. I'm not sure how sysdepends copes with unmodified object names in views...

  Tim Wilkinson

  "If it doesn't work in practice, you're using the wrong theory"
  - Immanuel Kant

• Bill Geake

  Ten Centuries

  Points: 1242

  More actions

  June 14, 2006 at 1:15 am

  #643560

  Thanks so much for this, Tim. I'm ashamed to say I haven't found time to try it out:

  • nasty deadline 
  • other problems (eg SP3 bug when calling lots of udfs from a view)
  • my workaround has got me away from the limit it seems

  The workaround was reasonably easy - as the main view that had the problem and its sub views had lots of UNIONs in them, I collected up the UNIONs with different WHERE clauses and used CASE statements to allow variation in the fields returned.

  I will post here any useful experience of running your code.

  Regards,

   

  Bill.

• Bill Geake

  Ten Centuries

  Points: 1242

  More actions

  June 23, 2006 at 4:16 am

  #645482

  Finally I got around to building this function and running it. Had a slight problem in that the syntax checker didn't like @level+1 as an argument, so I had to declare another variable, set it to @level+1 and use that instead.

  Problem now is that I want this to work across databases - most of my views reference tables from 2 or 3 databases. Any ideas? Or is there a tool that can do this that anyone can recommend?

  Thanks,

  Bill.