How can you access a database based on a value in a different database? (TSQL)

So I have a SQL Server running and I have two databases, the first database stores the name of the second database. In the first database I want to create a statement that modifies data in the second table, something along the lines of:

(This is a query from the first database)

select * from [select database_name from table1].table
开发者_如何学JAVA

I have been playing around with the sys. keyword trying to figure out if I could do this but have had no luck. Any help would be appreciated, thanks.

---

You'd have to build a string and execute it as dynamic SQL. See: The Curse and Blessings of Dynamic SQL

declare @database_name sysname

select @database_name = database_name from Table1

declare @sql nvarchar(1000)

set @sql = N'select * from ' + @database_name + N'.SchemaName.TableName'

exec sp_executesql @sql

---

you can do this only by executing a dynamic query like this :

 DECLARE @base varchar(30)
 SELECT @base = "yourOtherBase"
 EXECUTE ("select * from " + @base + "..table");

---

One way would be to set up some dynamic SQL, along the lines of:

DECLARE @Command varchar(1000)

SELECT @Command = replace('select * from <TargetDB>.dbo.table', '<TargetDB>', tabel1.database_name)
 from table1
 where [your criteria here]

EXECUTE (@Command)

Another way would be to create views (or synonyms or linked server definitions) that reference the "target" database, e.g.

DECLARE @Command varchar(1000)

SELECT @Command = replace('CREATE VIEW SomeView AS select * from <TargetDB>.dbo.table', '<TargetDB>', tabel1.database_name)
 from table1
 where [your criteria here]

EXECUTE (@Command)

This would create a view that could be referenced by permanent (that is, non-dynamic) code. This wouldn't work so well if the targe database changes frequently, or if you have a lot of target databases.

But honestly, the real answer is that no, you can't do this at all easily, and you should do your best to avoid situations where you have to do this. Trust me on this one, it can be a debugging and maintenance nightmare.