Way to switch to a different database from queries.

[EM1107]

Good day guys I have one question here and hope this is possible.

I would like to know if it is possible to run a query that would switch to a different database.
I know that if select the command use db GO; db being the database I want to use would go to that database but in my case it is a little different.

Here is what I want to do.

1. restore a database
2. Find the name of the database
3. from the name I want my script to select that database and run some code.

I.e. Restore database test.
If I use the following query I can get the name of the recent database I just restored.
SELECT NAME FROM MASTER..SYSDATABASES WHERE CRDATE = (SELECT MAX(CRDATE) FROM MASTER..SYSDATABASES)
AND NAME <> 'TEMPDB'

I would like to use the result and create a command using the result get my code to execute to that database I just restored without having to manually change the database from management studio.

Is this possible?

 

[johnherman]

SQL Server supports the "Linked Server" concept. Check out sp_addlinkedserver and use a syntax like:

select * from [server\instance].database.owner.tablename

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[Olaf Doschke]

You seem to not know about the freedom you indeed have. You can also combine data of all databases of the same instance via the full qualified name including database:

SELECT * from operative.dbo.table ot inner join test.dbo.table tt on ot.abc=tt.abc

You can take that and apply it with dynamic sql (sp_executesql) for the aspect of creating a query on the last restored db.
The linked server concept has the advantage to be able to address any database available via any ODBC driver or OLEDB Provider.

Doing this within SSMS I don't see much of a benefit. How hard is it to pick a db from the available databases combobox?

Bye, Olaf.

 

[djj55]

You will need dynamic SQL for this, if I am reading your post correctly. By using johnherman suggestion you can create the code you need

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[kjv1611]

I would like to know if it is possible to run a query that would switch to a different database.

For DATABASES, you just need to fully qualify, then no worry about switching:

SELECT DbOne.*
FROM DatabaseOne.TableOne DbOne
JOIN DatabaseTwo.TableTwo DbTwo
ON DbOne.MainID = DbTwo.MainID

If you're needing to switch between servers, then yeah, that gets more tricky, and I agree with djj55 in that you will likely need to use Dynamic SQL. If you're not familiar, that just means creating a VARCHAR varaible, inserting your SQL code into that variable, and running it. The cool thing is you can tell it to "EXEC @MySqlCode AT MyOtherSqlServerName" - or else it's ON instead of AT. I'm not at a machine with SQL installed, so I can't test and don't remember the exact syntax.

Be sure to post back, letting others know if you got the help you needed, or else clarify your needs.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57