.show database command
Applies to: ✅ Microsoft Fabric ✅ Azure Data Explorer
Returns a table showing the properties of the context database.
To return a table in which every record corresponds to a database in the cluster that the user has access to, see .show databases.
To return a table in which every record corresponds to a database in the eventhouse that the user has access to, see .show databases.
Permissions
You must have at least Database User, Database Viewer, or Database Monitor permissions to run this command. For more information, see role-based access control.
Syntax
.show database [details | identity | policies | datastats]
The default call without any options specified is equal to 'identity' option.
Learn more about syntax conventions.
Returns
This command returns a table with the following columns for each option:
Output for identity option
| Output parameter | Type | Description |
|---|---|---|
| DatabaseName | string |
The database name. Database names are case-sensitive. |
| PersistentStorage | string |
The persistent storage URI in which the database is stored. (This field is empty for ephemeral databases.) |
| Version | string |
Database version number. This number is updated for each change operation in the database (such as adding data and changing the schema). |
| IsCurrent | bool |
True if the database is the one that the current connection points to. |
| DatabaseAccessMode | string |
The database access mode. For example, if the database access mode is ReadOnly, then all requests to modify the database fail. |
| PrettyName | string |
The database pretty name. |
| CurrentUserIsUnrestrictedViewer | bool |
Specifies if the current user is an unrestricted viewer on the database. |
| DatabaseId | guid |
The database unique ID. |
| InTransitionTo | string |
Describes the database in a transition state. For instance, access mode change, detaching database, changing the database physical location, storage keys change, or database pretty name change. |
| SuspensionState | string |
When the database was suspended and the reason for the suspension. |
Output for details option
| Output parameter | Type | Description |
|---|---|---|
| DatabaseName | string |
The name of the database. Database names are case-sensitive. |
| PersistentStorage | string |
The persistent storage URI in which the database is stored. (This field is empty for ephemeral databases.) |
| Version | string |
Database version number. This number is updated for each change operation in the database (such as adding data and changing the schema). |
| IsCurrent | bool |
True if the database is the one that the current connection points to. |
| DatabaseAccessMode | string |
How the database is attached. For example, if the database is attached in ReadOnly mode, all requests to modify the database in any way, fail. Options include ReadWrite, ReadOnly, ReadOnlyFollowing, or ReadWriteEphemeral. |
| PrettyName | string |
The database pretty name, if any. |
| AuthorizedPrincipals | string |
The collection of authorized principals for the database, serialized in JSON format. |
| RetentionPolicy | string |
The database Retention policy, serialized in JSON format. |
| MergePolicy | string |
The database Extents Merge policy, serialized in JSON format. |
| CachingPolicy | string |
The database Caching policy, serialized in JSON format. |
| ShardingPolicy | string |
The database Sharding policy, serialized in JSON format. |
| StreamingIngestionPolicy | string |
The database Streaming Ingestion policy, serialized in JSON format. |
| IngestionBatchingPolicy | string |
The database Ingestion Batching policy, serialized in JSON format. |
| TotalSize | real |
The database extents total size in bytes. |
| DatabaseId | guid |
The database unique ID. |
| InTransitionTo | string |
Describes the database in a transition state. For instance, access mode change, detaching database, changing the database physical location, storage keys change, or database pretty name change. |
| NumberOfTables | int |
The number of tables in the database. |
| NumberOfExternalTables | int |
The number of external tables. |
| NumberOfMaterializedViews | int |
The number of materialized views. |
| EncryptionScope | int |
The encryption scope. |
| SuspensionState | string |
When the database was suspended and the reason. |
Output for policies option
| Output parameter | Type | Description |
|---|---|---|
| DatabaseName | string |
The name of the database. Database names are case-sensitive. |
| PersistentStorage | string |
The persistent storage URI in which the database is stored. (This field is empty for ephemeral databases.) |
| Version | string |
Database version number. This number is updated for each change operation in the database (such as adding data and changing the schema). |
| IsCurrent | bool |
True if the database is the one that the current connection points to. |
| DatabaseAccessMode | string |
How the database is attached. For example, if the database is attached in ReadOnly mode, all requests to modify the database in any way fail. Options include ReadWrite, ReadOnly, ReadOnlyFollowing, or ReadWriteEphemeral. |
| PrettyName | string |
The database pretty name, if any. |
| DatabaseId | guid |
The database unique ID. |
| AuthorizedPrincipals | string |
The collection of authorized principals for the database, serialized in JSON format. |
| RetentionPolicy | string |
The database Retention policy, serialized in JSON format. |
| MergePolicy | string |
The database Extents Merge policy, serialized in JSON format. |
| CachingPolicy | string |
The database Caching policy, serialized in JSON format. |
| ShardingPolicy | string |
The database Sharding policy, serialized in JSON format. |
| StreamingIngestionPolicy | string |
The database Streaming Ingestion policy, serialized in JSON format. |
| IngestionBatchingPolicy | string |
The database Ingestion Batching policy, serialized in JSON format. |
Output for datastats option
| Output parameter | Type | Description |
|---|---|---|
| DatabaseName | string |
The name of the database. Database names are case-sensitive. |
| PersistentStorage | string |
The persistent storage URI in which the database is stored. (This field is empty for ephemeral databases.) |
| Version | string |
Database version number. This number is updated for each change operation in the database (such as adding data and changing the schema). |
| IsCurrent | bool |
True if the database is the one that the current connection points to. |
| DatabaseAccessMode | string |
How the database is attached. For example, if the database is attached in ReadOnly mode, all requests to modify the database in any way fail. Options include ReadWrite, ReadOnly, ReadOnlyFollowing, or ReadWriteEphemeral. |
| PrettyName | string |
The database pretty name, if any. |
| DatabaseId | guid |
The database unique ID. |
| OriginalSize | real |
The database extents total original size*. |
| ExtentSize | real |
The database extents total size (data + indices)*. |
| CompressedSize | real |
The database extents total data compressed size*. |
| IndexSize | real |
The database extents total index size*. |
| RowCount | long |
The database extents total row count*. |
| HotOriginalSize | real |
The database hot extents total original size*. |
| HotExtentSize | real |
The database hot extents total size (data + indices)*. |
| HotCompressedSize | real |
The database hot extents total data compressed size*. |
| HotIndexSize | real |
The database hot extents total index size*. |
| HotRowCount | long |
The database hot extents total row count*. |
| TotalExtents | long |
The database total extents*. |
| HotExtents | long |
The database total hot extents*. |
* Values can be up to 15 minutes old, as they're taken from a cached summary of the database extents.
Examples
Show database identity
The following example shows the current database identity related properties.
.show database identity
Output
| DatabaseName | PersistentStorage | Version | IsCurrent | DatabaseAccessMode | PrettyName | CurrentUserIsUnrestrictedViewer | DatabaseId | InTransitionTo |
|---|---|---|---|---|---|---|---|---|
| SampleLogs | https://storagelocation01/abcdefg1234567 | v252.0 | true | ReadWrite | false | a1b2c3-1234-d4e5f7-8901234abc |
Show database policies
The following example shows the current database policies.
.show database policies
| DatabaseName | PersistentStorage | Version | IsCurrent | DatabaseAccessMode | PrettyName | DatabaseId | AuthorizedPrincipals | RetentionPolicy | MergePolicy | CachingPolicy | ShardingPolicy | StreamingIngestionPolicy | IngestionBatchingPolicy |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| SampleLogs | https://storagelocation01/abcdefg1234567 | v252.0 | true | ReadWrite | a1b2c3-1234-d4e5f7-8901234ab | [{ "Role": "Admin", "PrincipalName": "1234abcd-ef56-789-0123456e" "PrincipalType": "AadUser"}, { "Role": "Admin", "PrincipalName": "fghi1234-5678-9j12-3456", "PrincipalType": "AadUser"}, { "Role": "User", "PrincipalName": "kl1234-1234-56m7-89012", "PrincipalType": "AadApplication"}, { "Role": "User", "PrincipalName": "fghi1234-5678-9j12-3456" "PrincipalType": "AadApplication" }, { "Role": "Ingestor", "PrincipalName": "fghi1234-5678-9j12-3456", "PrincipalType": "AadApplication" }, { "Role": "Viewer", "PrincipalName": "#everyone#", "PrincipalType": "AadUser" }, {"Role": "Viewer", "PrincipalName": "#everyone#", "PrincipalType": "MsaUser" }, { "Role": "Admin" "PrincipalName":"nop1234-1234-56qr-7890", "PrincipalType": "AadUser"}, { "Role": "User", "PrincipalName": "nop1234-1234-56qr-7890", "PrincipalType": "AadUser" }, { "Role": "Viewer", "PrincipalName": "nop1234-1234-56qr-7890","PrincipalType": "AadApplication"}, {"Role": "Ingestor", "PrincipalName": "nop1234-1234-56qr-7890", "PrincipalType": "AadApplication"}] | null | { "RowCountUpperBoundForMerge": 16000000, "OriginalSizeMBUpperBoundForMerge": 30000, "MaxExtentsToMerge": 100, "MaxRangeInHours": 24, "AllowRebuild": true, "AllowMerge": true, "Lookback": { "Kind": 0,"CustomPeriod": null }, "Origin": 2 } | null | {"MaxRowCount": 750000, "MaxExtentSizeInMb": 1024, "MaxOriginalSizeInMb": 2048, "UseShardEngine": true, "ShardEngineMaxRowCount": 1048576, "ShardEngineMaxExtentSizeInMb": 8192, "ShardEngineMaxOriginalSizeInMb": 3072} | null | null |