When using SSMS, can you specify the database within a SQL Script?

Question

I work on multiple Azure databases, using AzureAD - Universal with MFA in SSMS.

I usually have a number of different scripts on the go, comparing results between the different databases, then an hr or so later, the connection is terminated by the server and if I try to re-run it, it tells me doesn't recognise the table, because it no longer knows which database to connect to.

Is there a way to specify the database within SSMS, if I try USE dbDocument before the script, it tells me Msg 40508, Level 16, State 1, Line 1 USE statement is not supported to switch between databases. Use a new connection to connect to a different database..

Is there any way around this, without having to right-click the db in question and hitting NEW QUERY?

Answer 1

The USE statement is not supported on Azure SQL Database. One solution is to split your work to several different steps according to the database that you want to use, and connect to the right database for each step.

If you want to execute the same query on multiple Azure Databases then you can use one of these options:

• Use horizontal partitioned databases based on shard map using the elastic database. Here you can find how to query a remote database.
• You can also use elastic queries.
• Or you can create your solution based on Azure Data Lake.

Answer 2

Looking at this MS doc, I doubt this is possible at all...

If a database other than the current database is provided, the USE statement does not switch between databases, and error code 40508 is returned. To change databases, you must directly connect to the database. The USE statement is marked as not applicable to SQL Database at the top of this page, because even though you can have the USE statement in a batch, it doesn't do anything.