how to use a variable to reference database names

Hi, I found your email address from discuss you posted on
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47384
I need to have a query like below to run to select all the fields from
different databases that each database have a table named 'table1'
select * from test...table1
select * from test1..table1
I have the following code sample:
declare @.dbname varchar(50)
set @.dbname='test'
select @.dbname
select * from @.dbname..table1
However I received the error messge when I ran this code. Can anyone
help to resolve this?
ThanksThats not working, you have to use dynamic sql then:
DECLARE @.SQLString VARCHAR(200)
SET @.dbname='test'
SET @.SQLString = 'SELECT * FROM ' + @.dbname + '..table1'
EXEC(@.SQLString)
HTH, jens Suessmeyer.|||set nocount on
go
set transaction isolation level read uncommitted
go
set quoted_identifier off
go
declare @.dbname varchar(128)
declare @.table varchar (128)
set @.table='table1'
declare cr_opendb cursor fast_forward for select name from
master.dbo.sysdatabases
open cr_opendb
fetch next from cr_opendb into @.dbname
while @.@.fetch_status = 0
begin
EXEC ("select b.name from "+@.dbname+".dbo.sysobjects a join
"+@.dbname+".dbo.syscolumns b on a.id=b.id where a.name='"+@.table+"'")
fetch next from cr_opendb into @.dbname
end
close cr_opendb
deallocate cr_opendb|||foru123 schrieb:
> Hi, I found your email address from discuss you posted on
> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47384
> I need to have a query like below to run to select all the fields from
> different databases that each database have a table named 'table1'
> select * from test...table1
> select * from test1..table1
> I have the following code sample:
> declare @.dbname varchar(50)
> set @.dbname='test'
> select @.dbname
> select * from @.dbname..table1
> However I received the error messge when I ran this code. Can anyone
> help to resolve this?
> Thanks
>
Dynamic SQL is what you are looking for. Look at "sp_executesql" in the
SQL Server BOL:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp
hth
Stefka|||Thats not working, you have to use dynamic sql then:
DECLARE @.SQLString VARCHAR(200)
SET @.dbname='test'
SET @.SQLString = 'SELECT * FROM ' + @.dbname + '..table1'
EXEC(@.SQLString)
HTH, jens Suessmeyer.|||Thats not working, you have to use dynamic sql then:
DECLARE @.SQLString VARCHAR(200)
SET @.dbname='test'
SET @.SQLString = 'SELECT * FROM ' + @.dbname + '..table1'
EXEC(@.SQLString)
HTH, jens Suessmeyer.|||set nocount on
go
set transaction isolation level read uncommitted
go
set quoted_identifier off
go
declare @.dbname varchar(128)
declare @.table varchar (128)
set @.table='table1'
declare cr_opendb cursor fast_forward for select name from
master.dbo.sysdatabases
open cr_opendb
fetch next from cr_opendb into @.dbname
while @.@.fetch_status = 0
begin
EXEC ("select b.name from "+@.dbname+".dbo.sysobjects a join
"+@.dbname+".dbo.syscolumns b on a.id=b.id where a.name='"+@.table+"'")
fetch next from cr_opendb into @.dbname
end
close cr_opendb
deallocate cr_opendb|||Thats not working, you have to use dynamic sql then:
DECLARE @.SQLString VARCHAR(200)
SET @.dbname='test'
SET @.SQLString = 'SELECT * FROM ' + @.dbname + '..table1'
EXEC(@.SQLString)
HTH, jens Suessmeyer.