Type of Database in Azure SQL Data Warehouse

Question

Is there a way to look at catalog data to determine if the database is an Azure SQL Data Warehouse database?

For example, I have a source database that I created through Azure, however I found that I could create another database in this Azure SQL Data Warehouse database without specifying EDITION or SERVICE_OBJECTIVE.

I assume this is an Azure SQL logical database, such as master, but it is not a system database, so I would like to determine the "type" of the database I am connecting to. So far, I have only thought about looking to see if certain pdw_ views exist, but I think that is probably the best way.

Thank you for your help!

Answer 1

Note. . The suggestion below works at the time of publication. This behavior may change in the future.

Today there is no easy way to do this. I added a function request to find the purpose of the service through TSQL. As a shortcut, you can run the following command:

SELECT
    name,
    recovery_model,
    recovery_model_desc
FROM
    sys.databases
WHERE
    name = DB_NAME();

If file recovery_model = 3 (SIMPLE), this is the SQL Data Warehouse database. If restore_model = 1 (FULL), this is an SQL database.

May 2016 update:

The following query will return DataWarehouse if you are connected to an Azure SQL DW database:

SELECT DATABASEPROPERTYEX(DB_NAME(), 'Edition') As Edition

# 2: sys.database_service_objectives DMV, :

SELECT
 db.[name] AS [Name],
 ds.[edition] AS [Edition],
 ds.[service_objective] AS [ServiceObject]
FROM
 sys.database_service_objectives ds
 JOIN sys.databases db ON ds.database_id = db.database_id