Assigning System Object Name to Variables

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 2005 Forums

Transact-SQL (2005)

Assigning System Object Name to Variables

Author

Topic

mivey4
Yak Posting Veteran

66 Posts

Posted - 2007-09-01 : 13:56:15

Hi.

I am having a problem trying to find the proper way to assign a system object (or rather in this case a system object name) to a variable to use in my T-SQL code.

I have to run a report across 20 or more databases and need to create a loop to run the same report execution on every database.

This is a snippet of the code I am trying to execute:
-- Create a temp table to store each database on the server in a table with a
-- corresponding incremental integer

create table #databases
(x int identity(1,1),dbase nvarchar(128))
go
insert into #databases (dbase)
select name from sys.databases
where name not in ('master','tempdb','model','msdb')

The problem is here when I try to test and see if I can switch to the first database in the table and perform a query:

select @dbase = dbase from #databases where x = 1
set @dbase = rtrim(@dbase)

use @dbase
go
select distinct customer_id from customers

Error Message -> Incorrect syntax near '@dbase'

Apparently assigning database names to variables is a bit more complicated than I suspected.

Can anyone provide a clue for how I can accomplish this task any other way or is there another way that I can assign the database name to a variable that could work???

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-01 : 14:20:43

you can't do this:
use @dbase

you'll have to do something like this:
exec('select distinct customer_id from ' + @dbase + '..customers');
or
exec('use ' + @dbase + '; select distinct customer_id from customers');

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

mivey4
Yak Posting Veteran

66 Posts

Posted - 2007-09-01 : 14:31:44

Thanks spirit1.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-03 : 05:51:42

IN and OUT of DSQL
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