.show database schema command
Applies to: ✅ Azure Data Explorer
The following commands show database schema as a table, JSON object, or CSL script.
Permissions
You must have at least Database User, Database Viewer, or Database Monitor permissions to run these commands. For more information, see role-based access control.
.show database schema
Syntax
.show database DatabaseName schema [details] [if_later_than "Version"]
.show databases (DatabaseName [, ...]) schema details
.show databases (DatabaseName if_later_than "Version" [, ...]) schema details
Learn more about syntax conventions.
Parameters
| Name | Type | Required | Description |
|---|---|---|---|
| DatabaseName | string |
✔️ | The name of the database for which to show the schema. |
| Version | string |
The version in "vMM.mm" format. MM represents the major version and mm represents the minor version. |
Returns
Returns a flat list of the structure of the selected databases with all their tables and columns in a single table or JSON object. When used with a version, the database is only returned if it's a later version than the version provided.
Examples
Show database schema
The following example displays the TestDB database schema. The database TestDB has one table called Events.
.show database TestDB schema
Output
| DatabaseName | TableName | ColumnName | ColumnType | IsDefaultTable | IsDefaultColumn | PrettyName | Version |
|---|---|---|---|---|---|---|---|
| TestDB | False | False | v.1.1 | ||||
| TestDB | Events | True | False | ||||
| TestDB | Events | Name | System.String | True | False | ||
| TestDB | Events | StartTime | System.DateTime | True | False | ||
| TestDB | Events | EndTime | System.DateTime | True | False | ||
| TestDB | Events | City | System.String | True | False | ||
| TestDB | Events | SessionId | System.Int32 | True | True |
Show database schema based on version
In the following example, the database is only returned if it's a later version than the version provided.
.show database TestDB schema if_later_than "v1.0"
Output
| DatabaseName | TableName | ColumnName | ColumnType | IsDefaultTable | IsDefaultColumn | PrettyName | Version |
|---|---|---|---|---|---|---|---|
| TestDB | False | False | v.1.1 | ||||
| TestDB | Events | True | False | ||||
| TestDB | Events | Name | System.String | True | False | ||
| TestDB | Events | StartTime | System.DateTime | True | False | ||
| TestDB | Events | EndTime | System.DateTime | True | False | ||
| TestDB | Events | City | System.String | True | False | ||
| TestDB | Events | SessionId | System.Int32 | True | True |
Because a version lower than the current database version was provided, the 'TestDB' schema was returned. Providing an equal or higher version would generate an empty result.
.show database schema as json
Syntax
.show database DatabaseName schema [if_later_than "Version"] as json
.show databases (DatabaseName [, ...]) schema as json [with (Options)]
.show databases (DatabaseName if_later_than "Version" [, ...]) schema as json [with (Options)]
Learn more about syntax conventions.
Parameters
| Name | Type | Required | Description |
|---|---|---|---|
| DatabaseName | string |
✔️ | The name of the database for which to show the schema. |
| Version | string |
The version in "vMM.mm" format. MM represents the major version and mm represents the minor version. | |
| Options | string |
A list of comma-separated key-value pairs used to determine which database entity schemas to return. If none are specified, then all entities are returned. See supported entity options. |
Supported entity options
The following table describes the values to provide for the Options parameter.
| Key | Value | Description |
|---|---|---|
Tables |
bool |
If true, tables are returned. |
ExternalTables |
bool |
If true, external tables are returned. |
MaterializedViews |
bool |
If true, materialized views are returned. |
Functions |
bool |
If true, functions are returned. |
Returns
Returns a flat list of the structure of the selected databases with all their tables and columns as a JSON object. When used with a version, the database is only returned if it's a later version than the version provided.
Examples
The following example returns the TestDB database schema in JSON format.
.show database TestDB schema as json
.show databases (TestDB, TestDB2) schema as json with(Tables=True, Functions=True)
Output
"{""Databases"":{""TestDB"":{""Name"":""TestDB"",""Tables"":{""Events"":{""Name"":""Events"",""DefaultColumn"":null,""OrderedColumns"":[{""Name"":""Name"",""Type"":""System.String""},{""Name"":""StartTime"",""Type"":""System.DateTime""},{""Name"":""EndTime"",""Type"":""System.DateTime""},{""Name"":""City"",""Type"":""System.String""},{""Name"":""SessionId"",""Type"":""System.Int32""}]}},""PrettyName"":null,""MajorVersion"":1,""MinorVersion"":1,""Functions"":{}}}}"
.show database schema as csl script
Generates a CSL script with all the required commands to create a copy of the given (or current) database schema.
Syntax
.show database DatabaseName schema as csl script [with (Options)]
Learn more about syntax conventions.
Parameters
| Name | Type | Required | Description |
|---|---|---|---|
| DatabaseName | string |
✔️ | The name of the database for which to show the schema. |
| Options | string |
A list of comma-separated key-value pairs used to determine what to return. See supported options. |
Supported options
| Key | Value | Description |
|---|---|---|
IncludeEncodingPolicies |
bool |
Defaults to true. If true, encoding policies at the database/table/column level are included. |
IncludeSecuritySettings |
bool |
Defaults to true. If true, the following options are included:- Authorized principals at the database/table level. - Row level security policies at the table level. - Restricted view access policies at the table level. |
IncludeIngestionMappings |
bool |
Defaults to true. If true, ingestion mappings at the table level are included. |
ShowObfuscatedStrings |
bool |
Defaults to false. If true, credentials persisted in Kusto configurations are returned. To use this option, you must either be a database admin or entity creator. If you don't have these permissions, the command fails. |
Returns
The script, returned as a string, contains:
- Commands to create all the tables in the database.
- Commands to set all database/tables/columns policies to match the original policies.
- Commands to create or alter all user-defined functions in the database.
Examples
CSL script
The following example generates a CSL script with the needed commands to recreate the TestDB database schema.
.show database TestDB schema as csl script
CSL script with obfuscated values
The following example generates a CSL script with the needed commands to recreate the TestDB database schema. It includes values that were obfuscated in the results of the previous example.
.show database TestDB schema as csl script with (ShowObfuscatedStrings = true)