.show databases command
Applies to: ✅ Microsoft Fabric ✅ Azure Data Explorer
Returns a table in which every record corresponds to a database in the cluster that the user has access to.
Returns a table in which every record corresponds to a database in the eventhouse that the user has access to.
For a table that shows the properties of the context database, see .show database.
Permissions
You must have at least AllDatabasesMonitor permissions to run this command.
Syntax
.show databases [details | identity | policies | datastats]
.show databases (DatabaseName [, ... ])
.show cluster databases [details | identity | policies | datastats]
.show cluster databases (DatabaseName [, ... ])
The databases and cluster databases commands are equivalent.
Learn more about syntax conventions.
Returns
This command returns a table with the following columns for each option:
Output for databases option
| Column name | Column type | Description |
|---|---|---|
| DatabaseName | string |
The name of the database. |
| PersistentStorage | string |
The persistent storage URI in which the database is stored. (This field is empty for ephemeral databases.) |
| Version | string |
Database version number. |
| 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 pretty name of the database, if any. |
| 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. |
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 identity 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. |
| 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. |
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
The following example shows databases that the user can access in the cluster.
The following example shows databases that the user can access in the eventhouse.
.show databases
Output
| DatabaseName | PersistentStorage | Version | IsCurrent | DatabaseAccessMode | PrettyName | DatabaseId | InTransitionTo | SuspensionState |
|---|---|---|---|---|---|---|---|---|
| SecurityLogs | https://storagelocation01/abcdefg1234567 | v25.0 | false | ReadWrite | a1b2c3-1234-d4e5f7-8901234abc | |||
| Samples | https://storagelocation/bcdefg2345678 | v3381.0 | true | ReadWrite | z9y8x7-1234-l5m6n7-8901234abc | |||
| SampleMetrics | https://storagelocation/cdefgh3456789 | v70179.0 | false | ReadWrite | w1v2u3-1234-d7e6f5-8901234abc | |||
| SampleLogs | https://storagelocation01/efghi4567890 | v252.0 | false | ReadWrite | s1r2q3-1234-t4e5f7-8901234abc |
| DatabaseName | PersistentStorage | Version | IsCurrent | DatabaseAccessMode | PrettyName | DatabaseId | InTransitionTo | SuspensionState |
|---|---|---|---|---|---|---|---|---|
| h1i2j3-1234-k4l5m7-8901234abc | https://storagelocation/abcdefg1234567 | v29.0 | true | ReadWrite | city-temp-data-1 | a1b2c3-1234-d4e5f7-8901234abc | ||
| s1r2q3-1234-t4e5f7-8901234abc | https://storagelocation01/bcdefg2345678 | v21.0 | false | ReadWrite | city-data-2 | X1y2z3-1234-d4e5f7-8901234abc |
Show databases identities
The following example shows the identities of the databases connected to the current eventhouse.
The following example shows the identities of the databases connected to the current cluster.
.show databases identity
Output
| DatabaseName | PersistentStorage | Version | IsCurrent | DatabaseAccessMode | PrettyName | CurrentUserIsUnrestrictedViewer | DatabaseId | InTransitionTo |
|---|---|---|---|---|---|---|---|---|
| Trender | https://storagelocation01/abcdefg1234567 | v45.0 | false | ReadWrite | false | a1b2c3-1234-d4e5f7-8901234abc | ||
| SampleMetrics | https://storagelocation/cdefgh3456789 | v70179.0 | false | ReadWrite | false | w1v2u3-1234-d7e6f5-8901234abc | ||
| SecurityLogs | https://storagelocation/bcdefg2345678 | v25.0 | false | ReadWrite | false | a1b2c3-1234-d4e5f7-8901234abc | ||
| SampleLogs | https://storagelocation01/abcdefg1234567 | v252.0 | true | ReadWrite | false | a1b2c3-1234-d4e5f7-8901234abc | ||
| Samples | https://storagelocation/bcdefg2345678 | v3381.2 | true | ReadWrite | false | z9y8x7-1234-l5m6n7-8901234abc | ||
| FindMyPartner | https://storagelocation01/zabcdef0123456 | v37.0 | false | ReadWrite | false | z1b2c3-1234-d4e5f7-8901234abc | ||
| SampleIoTData | https://storagelocation01/yzabcd0123456 | v145.0 | false | ReadWrite | false | y1b2c3-1234-d4e5f7-8901234abc | ||
| ContosoSales | https://storagelocation01/xabcdef0123456 | v241.7 | false | ReadWrite | false | x1b2c3-1234-d4e5f7-8901234abc |
| DatabaseName | PersistentStorage | Version | IsCurrent | DatabaseAccessMode | PrettyName | CurrentUserIsUnrestrictedViewer | DatabaseId | InTransitionTo |
|---|---|---|---|---|---|---|---|---|
| h1i2j3-1234-k4l5m7-8901234abc | ttps://storagelocation/abcdefg1234567 | v33.0 | true | ReadWrite | city-temp-data-1 | false | a1b2c3-1234-d4e5f7-8901234abc | |
| s1r2q3-1234-t4e5f7-8901234abc | https://storagelocation01/bcdefg2345678 | v25.0 | false | ReadWrite | city-temp-data-2 | false | a1b2c3-1234-d4e5f7-8901234abc |
Show databases datastats
The following example shows the datastats of the databases connected to the current eventhouse.
The following example shows the datastats of the databases connected to the current cluster.
.show databases datastats
Output
| DatabaseName | PersistentStorage | Version | IsCurrent | DatabaseAccessMode | PrettyName | DatabaseId | OriginalSize | ExtentSize | CompressedSize | IndexSize | RowCount | HotOriginalSize | HotExtentSize | HotCompressedSize | HotIndexSize | HotRowCount | TotalExtents | HotExtents |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Trender | https://storagelocation01/abcdefg1234567 | v45.0 | false | ReadWrite | a1b2c3-1234-d4e5f7-8901234abc3 | 3,110,418,064 | 336,847,996 | 289,726,808 | 47,121,188 | 31,104,180 | 3,110,418,064 | 336,847,996 | 289,726,808 | 47,121,188 | 31,104,180 | 33 | 33 | |
| SampleMetrics | https://storagelocation/cdefgh3456789 | v70179.0 | false | ReadWrite | w1v2u3-1234-d7e6f5-8901234abc | 475,587,055,800 | 27,214,500,605 | 22,987,534,959 | 4,226,965,646 | 1,124,828,461 | 475,587,055,800 | 27,214,500,605 | 22,987,534,959 | 4,226,965,646 | 1,124,828,461 | 182 | 182 | |
| SecurityLogs | https://storagelocation/bcdefg2345678 | v25.0 | false | ReadWrite | a1b2c3-1234-d4e5f7-8901234abc | 6,541,515 | 7,718,171 | 5,774,797 | 1,943,374 | 40,323 | 6,541,515 | 7,718,171 | 5,774,797 | 1,943,374 | 40,323 | 8 | 8 | |
| SampleLogs | https://storagelocation01/abcdefg123456721461682248 | v252.0 | false | ReadWrite | a1b2c3-1234-d4e5f7-8901234abc | 334,521,429,815 | 25,464,984,656 | 21,461,682,248 | 4,003,302,408 | 885,206,395 | 334,521,429,815 | 25,464,984,656 | 21,461,682,248 | 4,003,302,408 | 885,206,395 | 104 | 104 | |
| Samples | https://storagelocation/bcdefg2345678 | v3381.2 | true | ReadWrite | z9y8x7-1234-l5m6n7-8901234abc | 579,596,574,125 | 122,154,458,750 | 98,946,037,133 | 23,208,421,617 | 2,236,670,722 | 579,596,574,125 | 122,154,458,750 | 98,946,037,133 | 23,208,421,617 | 2,236,670,722 | 173 | 173 | |
| FindMyPartner | https://storagelocation01/zabcdef0123456 | v37.0 | false | ReadWrite | z1b2c3-1234-d4e5f7-8901234abc | 34,259 | 53,075 | 40,698 | 12,377 | 38 | 34,259 | 53,075 | 40,698 | 12,377 | 38 | 1 | 1 | |
| SampleIoTData | https://storagelocation01/yzabcd0123456 | v145.0 | false | ReadWrite | y1b2c3-1234-d4e5f7-8901234abc | 163,026,882 | 45,743,179 | 33,419,597 | 12,323,582 | 1,202,160 | 163,026,882 | 45,743,179 | 33,419,597 | 12,323,582 | 1,202,160 | 3 | 3 | |
| ContosoSales | https://storagelocation01/xabcdef0123456 | v241.7 | false | ReadWrite | x1b2c3-1234-d4e5f7-8901234abc | 18,506,982,546 | 4,256,663,908 | 3,602,512,666 | 654,151,242 | 12,092,847 | 18,506,982,546 | 4,256,663,908 | 3,602,512,666 | 654,151,242 | 12,092,847 | 10 | 10 |
| DatabaseName | PersistentStorage | Version | IsCurrent | DatabaseAccessMode | PrettyName | DatabaseId | OriginalSize | ExtentSize | CompressedSize | IndexSize | RowCount | HotOriginalSize | HotExtentSize | HotCompressedSize | HotIndexSize | HotRowCount | TotalExtents | HotExtents |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| h1i2j3-1234-k4l5m7-8901234abc | https://storagelocation/abcdefg1234567 | v33.0 | true | ReadWrite | city-temp-data-1 | a1b2c3-1234-d4e5f7-8901234abc | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| s1r2q3-1234-t4e5f7-8901234abc | https://storagelocation01/bcdefg2345678 | v25.0 | false | ReadWrite | city-temp-data-2 | a1b2c3-1234-d4e5f7-8901234abc | 239,026,900 | 18,422,618 | 15,104,815 | 3,317,803 | 618,794 | 239,026,900 | 18,422,618 | 15,104,815 | 3,317,803 | 618,794 | 1 | 1 |