repo.RepoObject_ColumnList - V
type: V ( view ), modify_date: 2022-09-05 16:54:30
RepoObject_guid: 6F90291C-9D61-EB11-84DC-A81E8446D5B0
Columns
PK | Column Name | Data Type | NULL? | ID |
---|---|---|---|---|
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
Indexes
idx_RepoObject_ColumnList__1
-
IndexSemanticGroup: no_group
-
[column-RepoObject_guid]; uniqueidentifier
-
-
PK, Unique, Real: 0, 0, 0
Column Details
_
PersistenceInsertColumnListSource
PersistenceInsertColumnListSource |
|
|
Referencing Columns
PersistenceTempTableColumnList
PersistenceTempTableColumnList |
|
|
Referencing Columns
sql_modules_definition
repo.RepoObject_ColumnList - V script
CREATE View repo.RepoObject_ColumnList
As
Select
--roc is the persistence target!
roc.RepoObject_guid
, CreateColumnList =
--
Concat (
String_Agg (
Concat (
--we need to convert to first argument nvarchar(max) to avoid the limit of 8000 byte
Cast('' As NVarchar(Max))
, Case
When 1 = 1
Then
Concat (
--
Cast('' As NVarchar(Max))
, QuoteName ( roc.RepoObjectColumn_name )
, ' '
, Case roc.Repo_is_computed
When 1
Then
Concat (
'AS '
, roc.Repo_definition
, Case
When roc.Repo_is_persisted = 1
Then
' PERSISTED'
End
)
Else
Concat (
roc.Repo_user_type_fullname
--CONSTRAINT
--DEFAULT
, Case
When roc.Repo_default_name <> ''
And IsNull (
roc.Repo_default_is_system_named
, 0
) = 0
Then
Concat (
' CONSTRAINT '
, roc.Repo_default_name
)
End
--
, Case
When roc.Repo_default_definition <> ''
Then
Concat (
' DEFAULT '
, roc.Repo_default_definition
)
End
--temporal table columns
, Case roc.Repo_generated_always_type
When 1
Then
' GENERATED ALWAYS AS ROW START'
When 2
Then
' GENERATED ALWAYS AS ROW END'
End
--IDENTITY
--, CASE roc.Repo_is_identity
-- WHEN 1
-- THEN ' IDENTITY ' + CASE
-- WHEN NOT roc.[Repo_seed_value] IS NULL
-- AND NOT roc.[Repo_increment_value] IS NULL
-- THEN CONCAT (
-- '('
-- , CAST(roc.[Repo_seed_value] AS NVARCHAR(max))
-- , ', '
-- , CAST(roc.[Repo_increment_value] AS NVARCHAR(max))
-- , ')'
-- )
-- END
-- END
, Case roc.Repo_is_identity
When 1
Then
Concat (
' IDENTITY ('
, IsNull (
Cast(roc.Repo_seed_value As NVarchar(Max))
, '1'
)
, ', '
, IsNull (
Cast(roc.Repo_increment_value As NVarchar(Max))
, '1'
)
, ')'
)
End
, Case
When roc.Repo_is_nullable = 0
Or roc.Repo_generated_always_type >= 1
Then
' NOT'
End
, ' NULL '
)
End
, Char ( 13 ) + Char ( 10 )
)
End
)
, ', '
) Within Group(Order By
roc.RepoObjectColumn_column_id)
, Iif(
Max ( rop.ExecutionLogId_action ) In
( 'i', 'u' )
, ', ' + Max ( persistence_ExecutionLogId_ColumnName.Parameter_value_result ) + ' bigint NULL'
+ Char ( 13 ) + Char ( 10 )
, '')
)
, TemporalTableColumnList =
--
Concat (
String_Agg (
Concat (
--we need to convert to first argument nvarchar(max) to avoid the limit of 8000 byte
Cast('' As NVarchar(Max))
, Case
When roc.Repo_generated_always_type In
( 1, 2 )
Then
Concat (
Iif(roc.Repo_generated_always_type = 2, ', ', '')
, QuoteName ( roc.RepoObjectColumn_name )
, ' '
, Concat (
roc.Repo_user_type_fullname
--temporal table columns
, Case roc.Repo_generated_always_type
When 1
Then
' GENERATED ALWAYS AS ROW START'
When 2
Then
' GENERATED ALWAYS AS ROW END'
End
, Char ( 13 ) + Char ( 10 )
, Case roc.Repo_generated_always_type
When 1
Then
' CONSTRAINT [DF_' + roc.RepoObject_name
+ '_' + roc.RepoObjectColumn_name
+ '] DEFAULT SYSUTCDATETIME()'
When 2
Then
' CONSTRAINT [DF_' + roc.RepoObject_name
+ '_' + roc.RepoObjectColumn_name
+ '] DEFAULT CONVERT(DATETIME2, ''9999-12-31 23:59:59.9999999'')'
End
)
, Char ( 13 ) + Char ( 10 )
)
End
)
, ''
) Within Group(Order By
roc.RepoObjectColumn_column_id)
, Iif(
Max ( rop.ExecutionLogId_action ) In
( 'i', 'u' )
, ', ' + Max ( persistence_ExecutionLogId_ColumnName.Parameter_value_result ) + ' bigint NULL'
+ Char ( 13 ) + Char ( 10 )
, '')
)
, DbmlColumnList =
--
Concat (
String_Agg (
Concat (
--we need to convert to first argument nvarchar(max) to avoid the limit of 8000 byte
Cast('' As NVarchar(Max))
, QuoteName ( roc.RepoObjectColumn_name, '"' )
, ' '
, roc.Repo_user_type_fullname
, ' '
, '['
--null or not null
, Case
When roc.Repo_is_nullable = 0
Or roc.Repo_generated_always_type >= 1
Then
'not'
End
, ' null'
--primary key or pk
, Case
When roc.is_index_primary_key = 1
Then
', pk'
End
--unique
--default: some_value
--Attention:
--number value starts blank: default: 123 or default: 123.456
--string value starts with single quotes: default: 'some string value'
--expression value is wrapped with parenthesis: default: `now() - interval '5 days'`
--boolean (true/false/null): default: false or default: null
--
, Case
When roc.Repo_default_definition <> ''
Then
Concat ( ', default: ', QuoteName ( roc.Repo_default_definition, '`' ))
End
--increment
, Case roc.Repo_is_identity
When 1
Then
', increment'
End
--note: 'string to add notes'
, Case
When Not roc.Description Is Null
Then
', Note: ''''''' + Char ( 13 ) + Char ( 10 )
+ Replace ( Replace ( roc.Description, '\', '\\' ), '''''''', '\''''''' )
+ Char ( 13 ) + Char ( 10 ) + ''''''''
End
, ']'
)
, Char ( 13 ) + Char ( 10 )
) Within Group(Order By
roc.RepoObjectColumn_column_id)
, Iif(
Max ( rop.ExecutionLogId_action ) In
( 'i', 'u' )
, ', ' + Max ( persistence_ExecutionLogId_ColumnName.Parameter_value_result ) + ' bigint NULL'
+ Char ( 13 ) + Char ( 10 )
, '')
)
, PersistenceCompareColumnList =
--
Stuff (
String_Agg (
Concat (
--we need to convert to first argument nvarchar(max) to avoid the limit of 8000 byte
Cast('' As NVarchar(Max))
, ''
, Case
When
--source should exists
Not roc.persistence_source_RepoObjectColumn_guid Is Null
--roc is the target colum. A column which should be ignored could or could not exist in the target
--it it exists, it needs to be exluded
--if not, it will not be in the list of columns
And IsNull ( roc.is_persistence_Ignore, 0 ) = 0
And IsNull ( roc.is_persistence_NoCompareButUpdate, 0 ) = 0
And IsNull ( roc.is_persistence_NoCompareNoUpdate, 0 ) = 0
And IsNull ( roc.is_query_plan_expression, 0 ) = 0
And roc.Repo_generated_always_type = 0
And roc.Repo_is_computed = 0
And roc.Repo_is_identity = 0
--do not compare PK
--issue: if the source column is marked as PK but the target column is not marked as PK, then this column is included
--to avoid this we would need to analyze also the source column properties
--or we could set [is_persistence_no_check] = 1
And roc.is_index_primary_key Is Null
Then
Concat (
'OR T.'
, QuoteName ( roc.RepoObjectColumn_name )
, ' <> S.'
, QuoteName ( roc.RepoObjectColumn_name )
, Case
When roc.Repo_is_nullable = 1
Then
Concat (
' OR (S.'
, QuoteName ( roc.RepoObjectColumn_name )
, ' IS NULL AND NOT T.'
, QuoteName ( roc.RepoObjectColumn_name )
, ' IS NULL)'
, ' OR (NOT S.'
, QuoteName ( roc.RepoObjectColumn_name )
, ' IS NULL AND T.'
, QuoteName ( roc.RepoObjectColumn_name )
, ' IS NULL)'
)
End
, Char ( 13 ) + Char ( 10 )
)
End
)
, ''
) Within Group(Order By
roc.RepoObjectColumn_column_id)
, 1
, 3
, ' '
)
, PersistenceInsertColumnList =
--
Concat (
Stuff (
String_Agg (
Concat (
--we need to convert to first argument nvarchar(max) to avoid the limit of 8000 byte
Cast('' As NVarchar(Max))
, ''
, Case
When
--source should exists
Not roc.persistence_source_RepoObjectColumn_guid Is Null
--roc is the target colum. A column which should be ignored could or could not exist in the target
--it it exists, it needs to be exluded
--if not, it will not be in the list of columns
And IsNull ( roc.is_persistence_Ignore, 0 ) = 0
--2022-09-05 #69
And IsNull ( roc.is_persistence_NoInsert, 0 ) = 0
And IsNull ( roc.is_query_plan_expression, 0 ) = 0
And roc.Repo_generated_always_type = 0
And roc.Repo_is_computed = 0
And roc.Repo_is_identity = 0
Then
Concat (
', '
, QuoteName ( roc.RepoObjectColumn_name )
, Char ( 13 ) + Char ( 10 )
)
End
)
, ''
) Within Group(Order By
roc.RepoObjectColumn_column_id)
, 1
, 2
, ' '
)
, Iif(
Max ( rop.ExecutionLogId_action ) In
( 'i', 'u' )
, ', ' + Max ( persistence_ExecutionLogId_ColumnName.Parameter_value_result )
--+ ' = @current_execution_log_id'
--
+ Char ( 13 ) + Char ( 10 )
, '')
)
, PersistenceInsertColumnListSource =
--
Concat (
Stuff (
String_Agg (
Concat (
--we need to convert to first argument nvarchar(max) to avoid the limit of 8000 byte
Cast('' As NVarchar(Max))
, ''
, Case
When
--source should exists
Not roc.persistence_source_RepoObjectColumn_guid Is Null
--roc is the target colum. A column which should be ignored could or could not exist in the target
--it it exists, it needs to be exluded
--if not, it will not be in the list of columns
And IsNull ( roc.is_persistence_Ignore, 0 ) = 0
--2022-09-05 #69
And IsNull ( roc.is_persistence_NoInsert, 0 ) = 0
And IsNull ( roc.is_query_plan_expression, 0 ) = 0
And roc.Repo_generated_always_type = 0
And roc.Repo_is_computed = 0
And roc.Repo_is_identity = 0
Then
Concat (
', '
, QuoteName ( roc.RepoObjectColumn_name )
, Char ( 13 ) + Char ( 10 )
)
End
)
, ''
) Within Group(Order By
roc.RepoObjectColumn_column_id)
, 1
, 2
, ' '
)
, Iif(
Max ( rop.ExecutionLogId_action ) In
( 'i', 'u' )
, ', ' + Max ( persistence_ExecutionLogId_ColumnName.Parameter_value_result )
--source column should use the variable, but the target doesn't contain this part
+ ' = @current_execution_log_id'
--
+ Char ( 13 ) + Char ( 10 )
, '')
)
, PersistenceTempTableColumnList =
--should contain all columns, also identity, computed and so on
--
Concat (
Stuff (
String_Agg (
Concat (
--we need to convert to first argument nvarchar(max) to avoid the limit of 8000 byte
Cast('' As NVarchar(Max))
, ''
, Case
When
--source should exists
Not roc.persistence_source_RepoObjectColumn_guid Is Null
--roc is the target colum. A column which should be ignored could or could not exist in the target
--it it exists, it needs to be exluded
--if not, it will not be in the list of columns
And IsNull ( roc.is_persistence_Ignore, 0 ) = 0
And IsNull ( roc.is_query_plan_expression, 0 ) = 0
--And roc.Repo_generated_always_type = 0
--And roc.Repo_is_computed = 0
--And roc.Repo_is_identity = 0
Then
Concat (
', '
, QuoteName ( roc.RepoObjectColumn_name )
, Char ( 13 ) + Char ( 10 )
)
End
)
, ''
) Within Group(Order By
roc.RepoObjectColumn_column_id)
, 1
, 2
, ' '
)
, Iif(
Max ( rop.ExecutionLogId_action ) In
( 'i', 'u' )
, ', ' + Max ( persistence_ExecutionLogId_ColumnName.Parameter_value_result )
--+ ' = @current_execution_log_id'
--
+ Char ( 13 ) + Char ( 10 )
, '')
)
, PersistenceUpdateColumnList =
--
Concat (
Stuff (
String_Agg (
Concat (
--we need to convert to first argument nvarchar(max) to avoid the limit of 8000 byte
Cast('' As NVarchar(Max))
, ''
, Case
When
--source should exists
Not roc.persistence_source_RepoObjectColumn_guid Is Null
--roc is the target colum. A column which should be ignored could or could not exist in the target
--it it exists, it needs to be exluded
--if not, it will not be in the list of columns
And IsNull ( roc.is_persistence_Ignore, 0 ) = 0
----is_persistence_NoCompareButUpdate => they should not be compared, but updated! That's why commented out
--And IsNull ( roc.is_persistence_NoCompareButUpdate, 0 ) = 0
And IsNull ( roc.is_persistence_NoCompareNoUpdate, 0 ) = 0
And IsNull ( roc.is_query_plan_expression, 0 ) = 0
And roc.Repo_generated_always_type = 0
And roc.Repo_is_computed = 0
And roc.Repo_is_identity = 0
Then
Concat (
', T.'
, QuoteName ( roc.RepoObjectColumn_name )
, ' = S.'
, QuoteName ( roc.RepoObjectColumn_name )
, Char ( 13 ) + Char ( 10 )
)
End
)
, ''
) Within Group(Order By
roc.RepoObjectColumn_column_id)
, 1
, 2
, ' '
)
, Iif(
Max ( rop.ExecutionLogId_action ) In
( 'u' )
, ', T.' + Max ( persistence_ExecutionLogId_ColumnName.Parameter_value_result )
+ ' = @current_execution_log_id' + Char ( 13 ) + Char ( 10 )
, '')
)
--2022-09-01
--required to generate views to compare source and target content
, PersistenceSrcTgtColumnList =
--
Concat (
Stuff (
String_Agg (
Concat (
--we need to convert to first argument nvarchar(max) to avoid the limit of 8000 byte
Cast('' As NVarchar(Max))
, ''
, Case
When
--source should exists
Not roc.persistence_source_RepoObjectColumn_guid Is Null
--roc is the target colum. A column which should be ignored could or could not exist in the target
--it it exists, it needs to be exluded
--if not, it will not be in the list of columns
And IsNull ( roc.is_persistence_Ignore, 0 ) = 0
----is_persistence_NoCompareButUpdate => they should not be compared, but updated! That's why commented out
--And IsNull ( roc.is_persistence_NoCompareButUpdate, 0 ) = 0
And IsNull ( roc.is_persistence_NoCompareNoUpdate, 0 ) = 0
And IsNull ( roc.is_query_plan_expression, 0 ) = 0
And roc.Repo_generated_always_type = 0
And roc.Repo_is_computed = 0
And roc.Repo_is_identity = 0
Then
Concat (
''
, ', '
, QuoteName ( roc.RepoObjectColumn_name + '_src' )
, ' = S.'
, QuoteName ( roc.RepoObjectColumn_name )
, Char ( 13 ) + Char ( 10 )
, ', '
, QuoteName ( roc.RepoObjectColumn_name + '_tgt' )
, ' = T.'
, QuoteName ( roc.RepoObjectColumn_name )
, Char ( 13 ) + Char ( 10 )
)
End
)
, ''
) Within Group(Order By
roc.RepoObjectColumn_column_id)
, 1
, 2
, ' '
)
, Iif(
Max ( rop.ExecutionLogId_action ) In
( 'u' )
, ', T.' + Max ( persistence_ExecutionLogId_ColumnName.Parameter_value_result )
+ ' = @current_execution_log_id' + Char ( 13 ) + Char ( 10 )
, '')
)
, RepoObject_fullname = Max ( roc.RepoObject_fullname )
From
--Columns of the persistence target object
repo.RepoObjectColumn_gross2 As roc
Left Join
repo.RepoObject_persistence As rop
On
rop.target_RepoObject_guid = roc.RepoObject_guid
Cross Join config.ftv_get_parameter_value ( 'persistence_ExecutionLogId_ColumnName', '' ) As persistence_ExecutionLogId_ColumnName
Where
--not [is_query_plan_expression], these are not real columms
roc.is_query_plan_expression Is Null
--we need the datatype, or it should be computed
And
(
Not roc.Repo_user_type_fullname Is Null
Or roc.Repo_is_computed = 1
)
Group By
roc.RepoObject_guid