SQL script Scope in Microsoft SQL Server Management Studio - runs against active DB or all DB's?

Question

I'm not very familiar with SQL Server, more familiar with MySQL.

I'm used to seeing everything in code, whereas SQL Server, some things are done with buttons. In MySQL, I'd execute USE testing_database and then I'd know my subsequent queries would execute against that database.

In SQL Server Management Studio, I see that my "Testing Database" is selected in the dropdown select. Does that mean that the queries in the script will execute ONLY against that database?

The script does not include any references to database names. It uses sp_msdependencies to populate a list of Views and then acts on that list. Will sp_msdependencies execute only against the current database?

There are several other databases besides the testing database that I do not want to touch with this test.

• anon: I think you'll find SQL Server and MySQL work pretty much the same, right down to the USE statement. Perhaps you should test some with simple SELECT statements
• Buttle Butkus: The difference, as a MySQL user, is that you don't have to use a USE statement to select a DB in MSSQL. You can select it with the GUI using the dropdown or in the Explorer-like sidebar.
• anon: In SQL Server Management Studio, you can select it from the drop-down, or you can use a USE statement. The drop down is a feature of the SSMS GUI. The USE statement is part of T-SQL, and would apply to any script applied to SQL Server via any method.
• anon: Also, note that selecting the database in the Object Explorer doesn't affect the current database in any open query windows. If you have focus in the Object Explorer, and open a new window, the new window will default to that database--that's the only case where Object Explorer controls your database context in a query window. You can subsequently change databases using either the drop-down or a USE statement.
• Buttle Butkus: @AMtwo after running this stored procedure on the testing DB, I was able to just select the production DB from the dropdown and execute the same procedure against it. At least, I think that's what happened.

Answer 1

If you execute a use database statement, subsequent queries will only execute against that db. However: this is only for the current tab.

In SSMS to see the connection context look in the bottom right corner of the current tab. You should see the current database name there.

Or you could query the current db with:

SELECT DB_NAME() AS [Current Database];

Note: it is possible to have multiple tabs open which can be connected to different databases or servers. If you have multiple tabs open and tiled it can get a bit confusing.

As an added precaution you could disconnect and close other tabs to prevent any accidents.

It is possible that a sql script can contain many sql statements, and could include another use database command which could change the connection part way through the script. You should read the script or search for this before executing scripts supplied by other people.

One last warning: it is also possible to connect to multiple database servers simultaneously using registered servers. However unless you deliberately chose to do this its extremely unlikely you would ever encounter this even accidentally.