Very tricky! Can you help? Counting in views!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Very tricky! Can you help? Counting in views!

Author

Topic

Tim F
Starting Member

35 Posts

Posted - 2006-03-09 : 11:48:56

Hi All,

I have to count up the number of tables used in each view, this is because I've been hitting the 256 table limit in sql server and need an accurate reading!. I've tried sysdepends but sadly this won't deal with multiple sub selects.

This select will count the pupils table only once (made up example).

select top 1 name
number_of_girls = (select number_of_girls from pupils where gender = 'F')
number_of_boys = (select number_of_boys from pupils where gender = 'M')
from person

Then there may be views within the view as well!

Can anyone help?

Thanks, Tim

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-03-09 : 13:41:52

Your example will scan the Pupils table twice, not once. You could create it as a cross-tab, which would only scan the table once.

Perhaps we could help you redesign your database so that it doesn't require views with 256 tables? That's just farcical.

Tim F
Starting Member

35 Posts

Posted - 2006-03-10 : 03:55:18

Sorry! Bad example then. I have a huge database with custom reporting hence there can be hundreds of tables joined together.

Is there any way to text search within a view?

Tim F
Starting Member

35 Posts

Posted - 2006-03-10 : 05:52:51

exec calcTableExpense 'rpt_vix_StudentSibling_710'

-------------------------

set nocount on
go
alter procedure calcTableExpense (@named_object sysname)

as
begin

declare @level int, @rows int
select @level = 1

declare @objectCounter table
(level int , objid int, name sysname, type varchar(2) )

insert @objectCounter (level , objid , name , type )
select @level, id, name, type
from sysobjects where name = @named_object
select @rows = @@rowcount

while @rows > 0
begin
select @level = @level + 1

insert @objectCounter (level , objid , name , type )
select distinct @level, d.depid, o.name, o.type
from sysdepends d
join sysobjects o
on o.id = d.depid
join @objectCounter oc
on d.id = oc.objid
and oc.level = @level -1 -- calculate depedencies of previous level
where oc.type ='V' -- only a view will incur further expense

select @rows = @@rowcount
end

select * from @objectCounter
select count(*) as expense from @objectCounter

return
end
go

exec calcTableExpense 'sims_via_event_instances'

Tim F
Starting Member

35 Posts

Posted - 2006-03-10 : 05:53:19

Above will fail with this view. Any ideas?

select distinct

[student_id] = stud.person_id
,[sibling_id] = sib.person_id
,[sibling_name] = coalesce(s.chosenname+' ','')+s.surname+coalesce(' '+s.RegGroup,'')
,[same_address] = case when studadr.address_id = sibadr.address_id
then 'T' else 'F'
end

from sims.stud_student stud
, sims.stud_student sib
, sims.stud_relation studrel
, sims.stud_relation sibrel
, sims.sims_via_address_current studadr
, sims.sims_via_address_current sibadr
, sims.rpt_vix_student_710 s

where
studrel.student_id=stud.person_id
and studrel.parental_responsibility='T'

and sibrel.student_id = sib.person_id
and sibrel.parental_responsibility='T'

and sibrel.contact_id = studrel.contact_id
and sib.person_id <> stud.person_id

and studadr.person_id = stud.person_id
and sibadr.person_id = sib.person_id

and s.id = sib.person_id

Tim F
Starting Member

35 Posts

Posted - 2006-03-10 : 05:59:20

level objid name type
----------- ----------- -------------------------------------------------------------------------------------------------------------------------------- ----
1 1718401291 rpt_vix_StudentSibling_710 V
2 827149992 stud_relation U
2 1678629023 stud_student U

expense
-----------
3

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources