repo.RepoObject_SqlCreateTable - V
type: V ( view ), modify_date: 2022-01-05 18:06:35
RepoObject_guid: 7990291C-9D61-EB11-84DC-A81E8446D5B0
Examples
Example 1. Usage
--get sql code CREATE OR ALTER TABLE for persistence tables
Select
RepoObject_guid
, RepoObject_fullname
, SqlCreateTable
--, DbmlTable
--, ConList
--, persistence_source_RepoObject_fullname
--, persistence_source_RepoObject_guid
--, persistence_source_SysObject_fullname
From
dhw_self.repo.RepoObject_SqlCreateTable
Where
Not persistence_source_RepoObject_guid Is Null
Order By
RepoObject_fullname;Columns
| PK | Column Name | Data Type | NULL? | ID |
|---|---|---|---|---|
1 |
|
|
||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
Indexes
PK_RepoObject_SqlCreateTable
-
IndexSemanticGroup: no_group
-
[column-RepoObject_guid]; uniqueidentifier
-
-
PK, Unique, Real: 1, 1, 0
sql_modules_definition
repo.RepoObject_SqlCreateTable - V script
/*
<<property_start>>exampleUsage
--get sql code CREATE OR ALTER TABLE for persistence tables
Select
RepoObject_guid
, RepoObject_fullname
, SqlCreateTable
--, DbmlTable
--, ConList
--, persistence_source_RepoObject_fullname
--, persistence_source_RepoObject_guid
--, persistence_source_SysObject_fullname
From
dhw_self.repo.RepoObject_SqlCreateTable
Where
Not persistence_source_RepoObject_guid Is Null
Order By
RepoObject_fullname;
<<property_end>>
*/
CREATE View repo.RepoObject_SqlCreateTable
As
Select
ro.RepoObject_guid
, DbmlTable = Concat (
'Table '
, QuoteName ( ro.RepoObject_fullname, '"' )
, '{'
, Char ( 13 ) + Char ( 10 )
, ColList.DbmlColumnList
--note: 'string to add notes'
, Case
When Not ro.Description Is Null
Then
Char ( 13 ) + Char ( 10 ) + 'Note: ''''''' + Char ( 13 ) + Char ( 10 )
+ Replace ( Replace ( ro.Description, '\', '\\' ), '''''''', '\''''''' )
+ Char ( 13 ) + Char ( 10 ) + ''''''''
Else
Null
End
--optional Settings [setting1: value1, setting2: value2, setting3, setting4]
, Char ( 13 )
, Char ( 10 )
, Case
When Not IndexList.DbmlIndexList Is Null
Then
Char ( 13 ) + Char ( 10 ) + 'indexes {' + Char ( 13 ) + Char ( 10 )
+ IndexList.DbmlIndexList + Char ( 13 ) + Char ( 10 ) + '}' + Char ( 13 )
+ Char ( 10 )
Else
Null
End
, '}'
, Char ( 13 ) + Char ( 10 )
)
, ro.RepoObject_fullname
, SqlCreateTable = Concat (
'USE ['
, dwhdb.dwh_database_name
, ']'
, Char ( 13 ) + Char ( 10 )
, 'GO'
, Char ( 13 ) + Char ( 10 )
, 'CREATE TABLE '
, ro.RepoObject_fullname
, ' ('
, Char ( 13 ) + Char ( 10 )
, ColList.CreateColumnList
, Case
When Exists
(
Select
1
From
repo.Index_SqlConstraint_PkUq As ConList
Where
ConList.parent_RepoObject_guid = ro.RepoObject_guid
)
Then
','
Else
Null
End
--CONSTRAINT PK, FK, depending on some settings
, ConList.ConList
--PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo])
, Case
When Exists
(
Select
1
From
repo.RepoObjectColumn As roc
Where
roc.RepoObject_guid = ro.RepoObject_guid
And roc.Repo_generated_always_type = 1
)
And Exists
(
Select
1
From
repo.RepoObjectColumn As roc
Where
roc.RepoObject_guid = ro.RepoObject_guid
And roc.Repo_generated_always_type = 2
)
Then
Concat (
', PERIOD FOR SYSTEM_TIME ('
, QuoteName ((
Select
Top ( 1 )
roc.RepoObjectColumn_name
From
repo.RepoObjectColumn As roc
Where
roc.RepoObject_guid = ro.RepoObject_guid
And roc.Repo_generated_always_type = 1
Order By
RepoObjectColumn_name
)
)
, ', '
, QuoteName ((
Select
Top ( 1 )
roc.RepoObjectColumn_name
From
repo.RepoObjectColumn As roc
Where
roc.RepoObject_guid = ro.RepoObject_guid
And roc.Repo_generated_always_type = 2
Order By
RepoObjectColumn_name
)
)
, ')'
, Char ( 13 ) + Char ( 10 )
)
Else
Null
End
, ')'
--WITH
--(
--SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [Application].[Cities_Archive] )
--)
, Case ro.Repo_temporal_type
When 2
Then
Concat (
Char ( 13 ) + Char ( 10 )
, 'WITH'
, Char ( 13 ) + Char ( 10 )
, '('
, Char ( 13 ) + Char ( 10 )
, 'SYSTEM_VERSIONING = ON ( HISTORY_TABLE = '
--, '[Application].[Cities_Archive]'
, Coalesce (
ro_hist.RepoObject_fullname
, Concat (
QuoteName ( IsNull (
Hist_Table_schema.Parameter_value_result
, ro.RepoObject_schema_name
)
)
, '.'
, QuoteName ( Concat (
ro.RepoObject_name
, Hist_Table_name_suffix.Parameter_value_result
)
)
)
)
, ' )'
, Char ( 13 ) + Char ( 10 )
, ')'
, Char ( 13 ) + Char ( 10 )
)
Else
Null
End
)
, SqlAlterTableAddPeriodForSystemTime =
--
Iif(ColList.TemporalTableColumnList <> ''
, Concat (
'USE ['
, dwhdb.dwh_database_name
, ']'
, Char ( 13 ) + Char ( 10 )
, 'GO'
, Char ( 13 ) + Char ( 10 )
, 'ALTER TABLE '
, ro.RepoObject_fullname
, Char ( 13 ) + Char ( 10 )
, ' ADD'
, Char ( 13 ) + Char ( 10 )
, ColList.TemporalTableColumnList
--PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo])
, Case
When Exists
(
Select
1
From
repo.RepoObjectColumn As roc
Where
roc.RepoObject_guid = ro.RepoObject_guid
And roc.Repo_generated_always_type = 1
)
And Exists
(
Select
1
From
repo.RepoObjectColumn As roc
Where
roc.RepoObject_guid = ro.RepoObject_guid
And roc.Repo_generated_always_type = 2
)
Then
Concat (
', PERIOD FOR SYSTEM_TIME ('
, QuoteName ((
Select
Top ( 1 )
roc.RepoObjectColumn_name
From
repo.RepoObjectColumn As roc
Where
roc.RepoObject_guid = ro.RepoObject_guid
And roc.Repo_generated_always_type = 1
Order By
RepoObjectColumn_name
)
)
, ', '
, QuoteName ((
Select
Top ( 1 )
roc.RepoObjectColumn_name
From
repo.RepoObjectColumn As roc
Where
roc.RepoObject_guid = ro.RepoObject_guid
And roc.Repo_generated_always_type = 2
Order By
RepoObjectColumn_name
)
)
, ')'
, Char ( 13 ) + Char ( 10 )
)
End
)
, Null)
, SqlAlterTableAddSystemVersioning =
--
Iif(ColList.TemporalTableColumnList <> ''
, Concat (
'USE ['
, dwhdb.dwh_database_name
, ']'
, Char ( 13 ) + Char ( 10 )
, 'GO'
, Char ( 13 ) + Char ( 10 )
, 'ALTER TABLE '
, ro.RepoObject_fullname
, Char ( 13 ) + Char ( 10 )
, 'SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = '
--, '[Application].[Cities_Archive]'
, Coalesce (
ro_hist.RepoObject_fullname
, Concat (
QuoteName ( IsNull (
Hist_Table_schema.Parameter_value_result
, ro.RepoObject_schema_name
)
)
, '.'
, QuoteName ( Concat (
ro.RepoObject_name
, Hist_Table_name_suffix.Parameter_value_result
)
)
)
)
, ' )'
, Char ( 13 ) + Char ( 10 )
, ' )'
, Char ( 13 ) + Char ( 10 )
)
, Null)
--ConstraintList
, ConList.ConList
, ro.persistence_source_RepoObject_fullname
, ro.persistence_source_RepoObject_guid
, ro.persistence_source_SysObject_fullname
From
repo.RepoObject_gross As ro
--column list should exist, otherwise CREATE statement will be invalid
Inner Join
repo.RepoObject_ColumnList As ColList
On
ColList.RepoObject_guid = ro.RepoObject_guid
Left Join
(
Select
Con.parent_RepoObject_guid
, ConList = String_Agg (
Concat (
--we need to convert to first argument nvarchar(max) to avoid the limit of 8000 byte
Cast(' ' As NVarchar(Max)), Con.SqlConstraint, Char ( 13 ), Char ( 10 )
)
, ','
)
From
repo.Index_SqlConstraint_PkUq As Con
Group By
Con.parent_RepoObject_guid
) As ConList
On
ConList.parent_RepoObject_guid = ro.RepoObject_guid
Left Join
(
Select
i.parent_RepoObject_guid
, DbmlIndexList = String_Agg (
Concat (
--we need to convert to first argument nvarchar(max) to avoid the limit of 8000 byte
Cast(' ' As NVarchar(Max))
, '('
, i.DbmlIndexColumnList
, ') '
, '['
, Case
--this doesn't work. but we define pk in DbmlColumnList
When i.is_index_primary_key = 1
Then
'pk'
When i.is_index_unique = 1
Then
'unique'
Else
'name:''' + i.index_name + ''''
End
, ']'
)
, Char ( 13 ) + Char ( 10 )
) Within Group(Order By
i.RowNumber_PkPerParentObject)
From
repo.Index_gross As i
Where
i.is_index_primary_key = 0
And
(
i.is_index_unique = 1
Or i.is_index_real = 1
)
Group By
i.parent_RepoObject_guid
) As IndexList
On
IndexList.parent_RepoObject_guid = ro.RepoObject_guid
Left Join
repo.RepoObject As ro_hist
On
ro_hist.RepoObject_guid = ro.Repo_history_table_guid
Cross Join config.ftv_get_parameter_value ( 'Hist_Table_schema', '' ) As Hist_Table_schema
Cross Join config.ftv_get_parameter_value ( 'Hist_Table_name_suffix', '' ) As Hist_Table_name_suffix
Cross Join config.ftv_dwh_database () As dwhdb