sql export to SQL Server: Table "[schema_aaa].[table_bbb]" => CREATE TABLE [[schema_aaa].[table_bbb]]

[aisbergde]

dbml doesn't yet support schemas. The only way to use them is:
Table "[schema_aaa].[table_bbb]"

When creating the sql then it is not what it could be, it is created like CREATE TABLE [[schema_aaa].[table_bbb]]
I am not sure if this can be solved or if it should be implemented when dbml will support it. But without schema support only tables in schema dbo could be created.

I only want to report the issues. Currently, it is not so important for me to solve them, because dbml doesn't yet support calculated columns and some other required features, and anyway I can not use it to define a database in dbml and create sql from dbml without these features. But for tables without calculated columns and for simpler databases it could work if there would be a solution for the schemas.

Other issues:

• "defaults"
  expressions are quoted with additional "()"
  it is OK, it is valid, so it could stay this way
• index
  column names are OK in the table definition, but in the index definition they are quoted with "" instead of []
• extended properties
  • when the target is MSSQL the name for exported descriptions should be "ms_description", but not "Column_description" or "Table_description"
  • issue with splitting into schema and table

example:
MS SQL Server

Table "[repo].[RepoObject]"{
"RepoObject_guid" uniqueidentifier [not null, pk, default: `(newsequentialid())`]
"has_execution_plan_issue" bit [ null]
"has_get_referenced_issue" bit [ null]
"Inheritance_StringAggSeparatorSql" nvarchar(4000) [ null]
"InheritanceDefinition" nvarchar(4000) [ null]
"InheritanceType" tinyint [ null]
"is_repo_managed" bit [ null]
"is_SysObject_missing" bit [ null]
"modify_dt" datetime [not null, default: `(getdate())`]
"pk_index_guid" uniqueidentifier [ null]
"Repo_history_table_guid" uniqueidentifier [ null]
"Repo_temporal_type" tinyint [ null, Note: '''
reference in [repo_sys].[type]
''']
"RepoObject_name" nvarchar(128) [not null, default: `(newid())`]
"RepoObject_Referencing_Count" int [ null]
"RepoObject_schema_name" nvarchar(128) [not null]
"RepoObject_type" char(2) [not null, Note: '''
reference in [repo_sys].[type]
''']
"SysObject_id" int [ null]
"SysObject_modify_date" datetime [ null]
"SysObject_name" nvarchar(128) [not null, default: `(newid())`]
"SysObject_parent_object_id" int [not null, default: `((0))`]
"SysObject_query_executed_dt" datetime [ null]
"SysObject_query_plan" xml [ null]
"SysObject_schema_name" nvarchar(128) [not null]
"SysObject_type" char(2) [ null, Note: '''
reference in [repo_sys].[type]
''']
"has_different_sys_names" bit [ null, Note: '''
(CONVERT([bit],case when [RepoObject_schema_name]<>[SysObject_schema_name] OR [RepoObject_name]<>[SysObject_name] OR [RepoObject_type]<>[SysObject_type] then (1) else (0) end))
''']
"is_RepoObject_name_uniqueidentifier" int [not null, Note: '''
(case when TRY_CAST([RepoObject_name] AS [uniqueidentifier]) IS NULL then (0) else (1) end)
''']
"is_SysObject_name_uniqueidentifier" int [not null, Note: '''
(case when TRY_CAST([SysObject_name] AS [uniqueidentifier]) IS NULL then (0) else (1) end)
''']
"node_id" bigint [ null, Note: '''
(CONVERT([bigint],[SysObject_id])*(10000))
''']
"RepoObject_fullname" nvarchar(261) [not null, Note: '''
(concat('[',[RepoObject_schema_name],'].[',[RepoObject_name],']'))
''']
"SysObject_fullname" nvarchar(261) [not null, Note: '''
(concat('[',[SysObject_schema_name],'].[',[SysObject_name],']'))
''']
"SysObject_query_sql" nvarchar(406) [not null, Note: '''
(concat('SELECT * FROM [',[repo].[fs_dwh_database_name](),'].[',[SysObject_schema_name],'].[',[SysObject_name],']'))
''']
"usp_persistence_name" nvarchar(140) [not null, Note: '''
('usp_PERSIST_'+[RepoObject_name])
''']

indexes {
 ( "SysObject_schema_name", "SysObject_name") [unique]
 ( "RepoObject_schema_name", "RepoObject_name") [unique]
}
}

this will create an mssql script

CREATE TABLE [[repo].[RepoObject]] (
  [RepoObject_guid] uniqueidentifier PRIMARY KEY NOT NULL DEFAULT ((newsequentialid())),
  [has_execution_plan_issue] bit,
  [has_get_referenced_issue] bit,
  [Inheritance_StringAggSeparatorSql] nvarchar(4000),
  [InheritanceDefinition] nvarchar(4000),
  [InheritanceType] tinyint,
  [is_repo_managed] bit,
  [is_SysObject_missing] bit,
  [modify_dt] datetime NOT NULL DEFAULT ((getdate())),
  [pk_index_guid] uniqueidentifier,
  [Repo_history_table_guid] uniqueidentifier,
  [Repo_temporal_type] tinyint,
  [RepoObject_name] nvarchar(128) NOT NULL DEFAULT ((newid())),
  [RepoObject_Referencing_Count] int,
  [RepoObject_schema_name] nvarchar(128) NOT NULL,
  [RepoObject_type] char(2) NOT NULL,
  [SysObject_id] int,
  [SysObject_modify_date] datetime,
  [SysObject_name] nvarchar(128) NOT NULL DEFAULT ((newid())),
  [SysObject_parent_object_id] int NOT NULL DEFAULT (((0))),
  [SysObject_query_executed_dt] datetime,
  [SysObject_query_plan] xml,
  [SysObject_schema_name] nvarchar(128) NOT NULL,
  [SysObject_type] char(2),
  [has_different_sys_names] bit,
  [is_RepoObject_name_uniqueidentifier] int NOT NULL,
  [is_SysObject_name_uniqueidentifier] int NOT NULL,
  [node_id] bigint,
  [RepoObject_fullname] nvarchar(261) NOT NULL,
  [SysObject_fullname] nvarchar(261) NOT NULL,
  [SysObject_query_sql] nvarchar(406) NOT NULL,
  [usp_persistence_name] nvarchar(140) NOT NULL
)
GO

CREATE UNIQUE INDEX [[repo].[RepoObject]_index_0] ON [[repo].[RepoObject]] ("SysObject_schema_name", "SysObject_name")
GO

CREATE UNIQUE INDEX [[repo].[RepoObject]_index_1] ON [[repo].[RepoObject]] ("RepoObject_schema_name", "RepoObject_name")
GO

EXEC sp_addextendedproperty
@name = N'Column_Description',
@value = 'reference in [repo_sys].[type]',
@level0type = N'Schema', @level0name = 'dbo',
@level1type = N'Table',  @level1name = '[repo].[RepoObject]',
@level2type = N'Column', @level2name = 'Repo_temporal_type';
GO

EXEC sp_addextendedproperty
@name = N'Column_Description',
@value = 'reference in [repo_sys].[type]',
@level0type = N'Schema', @level0name = 'dbo',
@level1type = N'Table',  @level1name = '[repo].[RepoObject]',
@level2type = N'Column', @level2name = 'RepoObject_type';
GO

EXEC sp_addextendedproperty
@name = N'Column_Description',
@value = 'reference in [repo_sys].[type]',
@level0type = N'Schema', @level0name = 'dbo',
@level1type = N'Table',  @level1name = '[repo].[RepoObject]',
@level2type = N'Column', @level2name = 'SysObject_type';
GO

EXEC sp_addextendedproperty
@name = N'Column_Description',
@value = '(CONVERT([bit],case when [RepoObject_schema_name]<>[SysObject_schema_name] OR [RepoObject_name]<>[SysObject_name] OR [RepoObject_type]<>[SysObject_type] then (1) else (0) end))',
@level0type = N'Schema', @level0name = 'dbo',
@level1type = N'Table',  @level1name = '[repo].[RepoObject]',
@level2type = N'Column', @level2name = 'has_different_sys_names';
GO

EXEC sp_addextendedproperty
@name = N'Column_Description',
@value = '(case when TRY_CAST([RepoObject_name] AS [uniqueidentifier]) IS NULL then (0) else (1) end)',
@level0type = N'Schema', @level0name = 'dbo',
@level1type = N'Table',  @level1name = '[repo].[RepoObject]',
@level2type = N'Column', @level2name = 'is_RepoObject_name_uniqueidentifier';
GO

EXEC sp_addextendedproperty
@name = N'Column_Description',
@value = '(case when TRY_CAST([SysObject_name] AS [uniqueidentifier]) IS NULL then (0) else (1) end)',
@level0type = N'Schema', @level0name = 'dbo',
@level1type = N'Table',  @level1name = '[repo].[RepoObject]',
@level2type = N'Column', @level2name = 'is_SysObject_name_uniqueidentifier';
GO

EXEC sp_addextendedproperty
@name = N'Column_Description',
@value = '(CONVERT([bigint],[SysObject_id])*(10000))',
@level0type = N'Schema', @level0name = 'dbo',
@level1type = N'Table',  @level1name = '[repo].[RepoObject]',
@level2type = N'Column', @level2name = 'node_id';
GO

EXEC sp_addextendedproperty
@name = N'Column_Description',
@value = '(concat("[",[RepoObject_schema_name],"].[",[RepoObject_name],"]"))',
@level0type = N'Schema', @level0name = 'dbo',
@level1type = N'Table',  @level1name = '[repo].[RepoObject]',
@level2type = N'Column', @level2name = 'RepoObject_fullname';
GO

EXEC sp_addextendedproperty
@name = N'Column_Description',
@value = '(concat("[",[SysObject_schema_name],"].[",[SysObject_name],"]"))',
@level0type = N'Schema', @level0name = 'dbo',
@level1type = N'Table',  @level1name = '[repo].[RepoObject]',
@level2type = N'Column', @level2name = 'SysObject_fullname';
GO

EXEC sp_addextendedproperty
@name = N'Column_Description',
@value = '(concat("SELECT * FROM [",[repo].[fs_dwh_database_name](),"].[",[SysObject_schema_name],"].[",[SysObject_name],"]"))',
@level0type = N'Schema', @level0name = 'dbo',
@level1type = N'Table',  @level1name = '[repo].[RepoObject]',
@level2type = N'Column', @level2name = 'SysObject_query_sql';
GO

EXEC sp_addextendedproperty
@name = N'Column_Description',
@value = '("usp_PERSIST_"+[RepoObject_name])',
@level0type = N'Schema', @level0name = 'dbo',
@level1type = N'Table',  @level1name = '[repo].[RepoObject]',
@level2type = N'Column', @level2name = 'usp_persistence_name';
GO