.show database schema commands
The following commands show database schema as a table, JSON object, or CSL script.
.show databases schema
Syntax
.show database DatabaseName schema [details] [if_later_than "Version"]
.show databases ( DatabaseName1, ...) schema details
.show databases ( DatabaseName1 if_later_than "Version", ...) schema details
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.
Note
The version should only be provided in "vMM.mm" format. MM represents the major version and mm represent the minor version.
Example
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 |
Example
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 schema as json
Syntax
.show database DatabaseName schema [if_later_than "Version"] as json
.show databases ( DatabaseName1, ...) schema as json [with(Options)]
.show databases ( DatabaseName1 if_later_than "Version", ...) schema as json [with(Options)]
Arguments
The following Options allow you to select a subset of entities for each database schema that is returned. When using options, only the selected entities are returned for each database schema. Otherwise, all entities are returned.
Tables: (true|false) - Iftrue, tables are returned.ExternalTables: (true|false) - Iftrue, external tables are returned.MaterializedViews: (true|false) - Iftrue, materialized views are returned.Functions: (true|false) - Iftrue, 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.
Example
.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)]
Arguments
The following Options are all optional:
IncludeEncodingPolicies: (true|false) - Iftrue, encoding policies at the database/table/column level will be included. Defaults tofalse.IncludeSecuritySettings: (true|false) - Defaults tofalse. Iftrue, the following options would be 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: (true|false) - Iftrue, ingestion mappings at the table level will be included. Defaults tofalse.
Returns
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.
Example
.show database TestDB schema as csl script
.show database TestDB schema as csl script with(IncludeSecuritySettings = true)