Azure跨库访问Schema表：能否省略数据库名仅用[Schema].[Table]格式？

Yes, you absolutely can set this up to avoid typing the full [DB1].DB1.table1 path every time! Here are the two most straightforward approaches to make the Schema.Table format work for cross-database access:

1. Create Synonyms (Recommended for Simple Cross-Database Access) #

Synonyms act as aliases for your remote tables, letting you reference them with the shorter Schema.Table syntax directly from DB2.

To create a synonym for [DB1].DB1.table1 in DB2:

CREATE SYNONYM DB1.table1 FOR [DB1].DB1.table1;

Repeat this for each table you need to access (like DB1.table2):

CREATE SYNONYM DB1.table2 FOR [DB1].DB1.table2;

Once created, you can query the table just like you would a local one:

SELECT * FROM DB1.table1;

Key Notes for Synonyms: #

• You need the CREATE SYNONYM permission in DB2 to set these up.
• The user accessing the synonym must also have the necessary permissions (like SELECT) on the original table in DB1.
• Synonyms are database-specific, so you'll need to create them in each database that needs to access DB1's tables.

2. Use External Tables (For Scalable or Cross-Instance Access) #

If you need a more scalable setup (or if your databases are on different logical servers), external tables are a solid option. This involves mapping DB1's tables as external objects in DB2.

First, create a database-scoped credential (for SQL authentication) in DB2:

CREATE DATABASE SCOPED CREDENTIAL DB1Credential
WITH IDENTITY = 'your_sql_username', SECRET = 'your_sql_password';

Then define the external data source pointing to DB1:

CREATE EXTERNAL DATA SOURCE DB1DataSource
WITH (
    LOCATION = 'sqlserver://your-logical-server.database.windows.net',
    DATABASE_NAME = 'DB1',
    CREDENTIAL = DB1Credential
);

Finally, create the external table that mirrors DB1.table1's schema:

CREATE EXTERNAL TABLE DB1.table1 (
    -- Match the column schema of DB1.table1 exactly
    Column1 INT,
    Column2 VARCHAR(50),
    -- Add all other columns here
)
WITH (
    LOCATION = 'DB1.table1',
    DATA_SOURCE = DB1DataSource
);

After setup, you can query DB1.table1 directly from DB2 just like a local table.

Important Considerations: #

• This method requires more initial work but works better for large numbers of tables or cross-server access.
• Ensure the credential has the appropriate permissions on DB1's tables.
• External tables don't auto-sync schema changes—you'll need to update them manually if the original table structure changes.

By default, Azure SQL Database requires specifying the database name for cross-database access since each database has its own independent namespace. The methods above create local mappings to bypass this requirement, enabling the Schema.Table syntax you want.

内容的提问来源于stack exchange，提问作者sakthi