Use with a variable - SQL Server Forums

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

Transact-SQL (2000)

Use with a variable

Author

Topic

toddles18@excite.com
Starting Member

1 Post

Posted - 2005-12-28 : 10:42:38

I'm working on a script that goes through all the databases on my server and collects statistics. I've got all the DB names in a temp table but when I try to do use @dbname I get an error. Here is my exact query.


Declare @Counter int, @DBName varchar(25), @TotalCount int
Set @Counter = 1
Select @TotalCount = count(*) from #tableinfo1

While @Counter <= @TotalCount
 Begin
  select @DBName=dbname from #tableinfo1 where [index] = @Counter
  USE @DBName
  print @DBName + Str(@Counter)
  Set @Counter = @Counter + 1
END

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-28 : 11:02:09

USE only takes a database name, not a variable. Here's an alternative using an SP that you would create in the master database which would act on the <current> database:


use master
set nocount on
go

--create an sp to collect db statistics
--name should start with 'sp_'
create proc sp_junk as select db_name()
go

create table #DBs (rowid int identity(1,1), dbname sysname)
insert #DBs
select name from master..sysDatabases

declare @rowid int
	,@dbname sysname
	,@sql nvarchar(100)

select @rowid = min(rowid) from #DBs
while @rowid is not null
begin
	select @dbname = dbname
		--command to execute from each database
		,@sql = 'exec ' + @dbname + '..sp_junk'
	from #DBs where rowid = @rowid

	--collect your stats
	exec sp_executesql @sql

	select @rowid = min(rowid) from #DBs where rowid > @rowid
end

go

drop table #DBs
drop proc sp_junk

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-29 : 01:22:57

USE @DBName is possible only if it is dynamically executed

EXEC
(' Use ' + @DBName +
' Other stuff '
)

that requires everything on a single block which is not recommended

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources