.show database schema command

  • Article
  • 10/18/2024

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)