How can I query multiple databases by name that reside on a Azure SQL server using SSMS?

user_782880 0
Viewed 1508

I want to be able to run some ad-hoc queries to get some fast results. The following will return the number of rows in table foobar from two databases that have identical structures.

USE Master
GO
select count(*) from MyFirstDB.dbo.foobar;
select count(*) from MySecondDB.dbo.foobar;

This works fine for SQL Server, but SQL Azure returns errors. I read that you with SQL Azure you cannot change the database context in the query window of SSMS. Is there a way to make this work? What happens if I want to create a join across two databases?

azure-sql-server sql-server
1 Answers

Ones needs to create external data source and table that is linked to another server. You can use the following code to create one.

CREATE DATABASE SCOPED CREDENTIAL credname
WITH IDENTITY = 'username',
SECRET = 'password';

CREATE EXTERNAL DATA SOURCE data_source_name
WITH
(
 TYPE=RDBMS,
 LOCATION='server.database.windows.net',
 DATABASE_NAME='databasename',
 CREDENTIAL= credname
);

CREATE EXTERNAL TABLE [dbo].[external_table_name](
 -- Copy column definition
 [Id] [uniqueidentifier] NOT NULL,
 [Name] [nvarchar](200) NULL
)
WITH
(
DATA_SOURCE = data_source_name,
SCHEMA_NAME = 'dbo',
OBJECT_NAME = 'tablename'
)

select *
from dbo.[external_table_name]

DROP EXTERNAL TABLE [external_table_name]

DROP EXTERNAL DATA SOURCE [data_source_name]

DROP DATABASE SCOPED CREDENTIAL credname
user_34192160
Related