In SQL Server 2008 R2 Management Studio, if I right-click on an object inside a specific database and choose "Select top 1000 rows ..", the database connection for the query window always opens on 'master' while the table name is fully qualified as [database].[dbo].[table]. This makes it impossible to jump in and tweak out this query and insert joins, etc., to the statement without also fully-qualifying everything I add, or add a USE statement, or select the database from the drop-down menu.
Is there a setting or something that will make query windows open with a database connection of the selected object browser's database rather than connect to 'master', and not fully qualify the object's database in the query text? I realize that I can register my SQL connection to default to my database, but we actually go through multiple new databases every week--in a given month I will have touched tens of databases--so it would be difficult to manage multiple database registrations. I would rather it if SSMS just connected to the specified database. Possible and straightforward?
There is no such setting for the SELECT TOP command, but you may be able to do this by changing the default database for your login. This is tedious if you're doing this often for various databases (much like changing the registrations, as I just noticed you already outlined).
Instead of using SELECT TOP 1000 (which in addition to not putting you in the right database context, also puts a TOP in that I assume you're just going to remove as well), you should right-click the table and choose Script Table as > SELECT to > New Query Window. This puts the context in the right DB, adds a USE command, doesn't have a TOP and doesn't database-prefix the table name.
EDIT
FWIW, I tend to agree with you, and I hate inconsistent behavior between functionality that should be similar/identical. So, I filed this suggestion - please vote!
http://connect.microsoft.com/SQLServer/feedback/details/684247/
If you are going in via Win Auth, are in a group, are using SA or some other userid, or are in a situation where changing your login is not really the solution, AND if all you wish to do is default to a database in the query editor:
In an existing open query editor, right-click, select Connection, Change Connection.
Click the Options button to expand the options.
In the Connection Properties tab, select the database you wish to connect to.
SSMS will remember your selection for that server. You may have to repeat for other servers, but it does remedy having a default database other than master.
If you want query window connects to some database by default, in SSMS go to the Security -> Logins, select the login that you use to connect to this server, and loock at the properties window. In page 'general' change the default database from 'master' to database you want to connect.
You could just put a USE [database name] at the top of the query window prior to executing a query. You do not need to fully qualify the database names if you do this. If you generate any scripts and version control them, this is a good practice to put at the top anyway. It at least prevents executing the script erroneously against the wrong database (say creation of a stored procedure).
USE MySpecialDatabase
GO
SELECT * FROM MySpecialTable
