INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Way to switch to a different database from queries.

Way to switch to a different database from queries.

(OP)
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?

RE: Way to switch to a different database from queries.

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


RE: Way to switch to a different database from queries.

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:

CODE

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.

RE: Way to switch to a different database from queries.

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!

RE: Way to switch to a different database from queries.

Quote (EM1107)

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close