Skip to content

azure-sql.md

Latest commit

 

History

History
45 lines (33 loc) · 3.74 KB

azure-sql.md

File metadata and controls

45 lines (33 loc) · 3.74 KB
author jaspkaur28
ms.service resource-graph
ms.topic include
ms.date 07/07/2022
ms.author jaspkaur

List SQL Databases and their elastic pools

The following query uses leftouter join to bring together SQL Database resources and their related elastic pools if any exist.

Resources
| where type =~ 'microsoft.sql/servers/databases'
| project databaseId = id, databaseName = name, elasticPoolId = tolower(tostring(properties.elasticPoolId))
| join kind=leftouter (
	Resources
	| where type =~ 'microsoft.sql/servers/elasticpools'
	| project elasticPoolId = tolower(id), elasticPoolName = name, elasticPoolState = properties.state)
	on elasticPoolId
| project-away elasticPoolId1

Azure CLI

az graph query -q "Resources | where type =~ 'microsoft.sql/servers/databases' | project databaseId = id, databaseName = name, elasticPoolId = tolower(tostring(properties.elasticPoolId)) | join kind=leftouter ( Resources | where type =~ 'microsoft.sql/servers/elasticpools' | project elasticPoolId = tolower(id), elasticPoolName = name, elasticPoolState = properties.state) on elasticPoolId | project-away elasticPoolId1"

Azure PowerShell

Search-AzGraph -Query "Resources | where type =~ 'microsoft.sql/servers/databases' | project databaseId = id, databaseName = name, elasticPoolId = tolower(tostring(properties.elasticPoolId)) | join kind=leftouter ( Resources | where type =~ 'microsoft.sql/servers/elasticpools' | project elasticPoolId = tolower(id), elasticPoolName = name, elasticPoolState = properties.state) on elasticPoolId | project-away elasticPoolId1"

Portal