Using a databasename in a variabele

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)

Using a databasename in a variabele

Author

Topic

Pika
Starting Member

9 Posts

Posted - 2003-08-06 : 04:02:24

Hi,

I am trying to create a cursor, wich reads all the user databasenames from a SQL Server(7) and then uses those databasenames in a query. However, sql does not seem to accept a variabele databasename. e.g.

USE @DATABASE_NAME
or
SELECT * FROM @DATABASE_NAME

are not accepted.
Any ideas to solve this?

Thanks!

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-08-06 : 04:37:42

Hi. You'll have to use dynamic SQL. Like so:

declare @db varchar(50),
@table varchar(50),
@sql varchar(2000)

select @sql = 'select * from ' + @db + '..' + @table

exec (@sql)

Pika
Starting Member

9 Posts

Posted - 2003-08-06 : 04:49:13

Andraax,
it works great, just as I wanted it!
Harstikke bedankt!
(Dutch for thank you very, very, very much)

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-06 : 05:04:46

... or you may use this undocumented stored procedure (for each DB):

EXEC sp_MSforeachdb @command1='select * from ?.dbo.t'

Here '?' stands for each DB name...

- Vit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources