dynamic sql or variable as tablename

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

General SQL Server Forums

New to SQL Server Programming

dynamic sql or variable as tablename

Author

Topic

geoffgin
Starting Member

1 Post

Posted - 2012-03-21 : 04:57:39

hello,

Just like to start by saying that i'm not a programmer but would really appreciate some help. Trying to pull some statistics out of some legacy DB's.

I originally put this together using a sing pair of DB's thinking i would simply declare db's as variables....i see now i can't. So if there is a workaround some could suggest or alternatively some help putting together a dynamic query that would achieve the same result it would be great

I should mention this is just a small sample of the total script and whatever solution should be scalable.

declare @DBname1 NVARCHAR(256),
@DBname2 NVARCHAR(256),
@box NVARCHAR(256)

/*----------------------------------------------------------------------------*/
/* Edit this section only */
/*----------------------------------------------------------------------------*/

set @DBname1 = 'ms1_state'
set @DBname2 = 'ms1_users'
set @box = 'box1'

/*----------------------------------------------------------------------------*/
/* End of editable section */
/*----------------------------------------------------------------------------*/

create table #perftemp

(

[Type] nvarchar (250),

[-50] int, [50-99] int,

[100-249] int,

[250-499] int,

[500-999] int,

[1000-9999] int,

[10000+] int,

[Max] int,

[Avg] int,

[Min] int

)
select si.AccountKey, count(distinct si.FileItemKey)as 'Item Count' into #perftemp2 ' from @dbname1 + 'dbo.fileitem' si with (nolock)
inner join @dbname1 + 'dbo.account' sa on sa.accountkey = si.accountkey
full join @DBname2 + '.dbo.usercfg gu on gu.id = sa.usercfgid
inner join @DBname2 + '.dbo.Users ga on ga.UserCfgId = gu.id
inner join @DBname2 + '.dbo.Account gm on gm.userid = ga.userid
inner join @DBname2 + '.dbo.ServiceInstances gi on gi.ServiceInstanceId = gm.ServiceInstanceId
inner join @DBname2 + '.dbo.Servers gb on gb.ServerId = gi.InstalledOnServerId
where gb.hostname like '%@box%'
group by si.AccountKey'
insert into #perftemp ([Type]) values ('file')
update #perftemp set [-50] = (select count([Item Count]) from #perftemp2 where [Item Count] < 50) where Type = 'file'
update #perftemp set [50-99] = (select count([Item Count]) from #perftemp2 where [Item Count] >= 50 and [Item Count]!> 99) where Type = 'File'
update #perftemp set [100-249] = (select count([Item Count]) from #perftemp2 where [Item Count] >= 100 and [Item Count]!> 249) where Type = 'file'
update #perftemp set [250-499] = (select count([Item Count]) from #perftemp2 where [Item Count] >= 250 and [Item Count]!> 499) where Type = 'file'
update #perftemp set [500-999] = (select count([Item Count]) from #perftemp2 where [Item Count] >= 500 and [Item Count]!> 999) where Type = 'file'
update #perftemp set [1000-9999] = (select count([Item Count]) from #perftemp2 where [Item Count] >= 1000 and [Item Count]!> 9999) where Type = 'file'
update #perftemp set [10000+] = (select count([Item Count]) from #perftemp2 where [Item Count] >= 10000) where Type = 'file'
update #perftemp set [Max] = (select Max ([Item Count]) from #perftemp2) where Type = 'file'
update #perftemp set [Avg] = (select avg ([Item Count]) from #perftemp2) where Type = 'file'
update #perftemp set [Min] = (select Min ([Item Count]) from #perftemp2) where Type = 'file'

drop table #perftemp

select * from #perftemp2

drop table #perftemp2

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-03-21 : 08:05:14

Refer this

www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources