title | description | ms.reviewer | ms.topic | ms.date |
---|---|---|---|---|
.show database schema command |
Learn how to use the `.show database schema` command to show the database schema as a table, JSON object, or CSL script. |
orspodek |
reference |
05/24/2023 |
The following commands show database schema as a table, JSON object, or CSL script.
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
DatabaseName schema
[details
] [if_later_than
"Version"]
.show
databases
(
DatabaseName [,
...])
schema
details
.show
databases
(
DatabaseName if_later_than
"Version" [,
...])
schema
details
[!INCLUDE syntax-conventions-note]
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 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.
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 |
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 have generated an empty result.
.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)
]
[!INCLUDE syntax-conventions-note]
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. |
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 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.
.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"":{}}}}"
Generates a CSL script with all the required commands to create a copy of the given (or current) database schema.
.show
database
DatabaseName schema
as
csl
script
[with
(
Options)
]
[!INCLUDE syntax-conventions-note]
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. |
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. |
The script, returned as a string, will contain:
- 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.
.show database TestDB schema as csl script
.show database TestDB schema as csl script with (ShowObfuscatedStrings = true)