.show database command

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.

Run the query

.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.