.show database command

  • Article

Applies to: ✅ 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.

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