There seems to be a problem in the ctrl-space object name autocompletion when using fully-qualified database names.
When you reference a table in the current database you can see the field list whether or not you reference it using the fully-qualified databasename.schemaname.objectname syntax
However, referencing another database on the same server using the fully-qualified databasename.schemaname.objectname syntax fails to show the field list unless you also remember to change the current database in the dropdown list.
I do a number of cross-database queries so have to change the current database multiple times when developing if I want to use autocomplete and then need to remember to set it back to the one I want before execution.
It would be good if the autocomplete could pick out the database from the 3-part naming syntax and show the autocomplete field list for tables correctly.
Even better enhancements would be to recognise the 4-part naming syntax for linked servers and to look upwards for the last USE statement in the SQL if the database is not explicitly stated.
I've attached some screenshots for clarification. In each of these screenshots I've just pressed ctrl-space to open the autocomplete list.
The screenshots all use the following test databases and tables:
CREATE DATABASE tstdb1
GO
USE tstdb1
GO
CREATE TABLE db1tbl1 (
id TINYINT NOT NULL IDENTITY PRIMARY KEY
, data CHAR(10)
)
GO
CREATE DATABASE tstdb2
GO
USE tstdb2
GO
CREATE TABLE db2tbl1 (
id TINYINT NOT NULL IDENTITY PRIMARY KEY
, data CHAR(10)
)
GO
I'm using SQL Server 2008 via the jTDS driver.
