repo.SysColumn_RepoObjectColumn_via_guid - V
type: V ( view ), modify_date: 2022-01-05 18:06:34
RepoObject_guid: 5E90291C-9D61-EB11-84DC-A81E8446D5B0
Columns
PK | Column Name | Data Type | NULL? | ID |
---|---|---|---|---|
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
Indexes
idx_SysColumn_RepoObjectColumn_via_guid__1
-
IndexSemanticGroup: no_group
-
[column-RepoObjectColumn_guid]; uniqueidentifier
-
-
PK, Unique, Real: 0, 0, 0
idx_SysColumn_RepoObjectColumn_via_guid__2
-
IndexSemanticGroup: no_group
-
[column-RepoObjectColumn_guid]; uniqueidentifier
-
[column-SysObjectColumn_name]; nvarchar(128)
-
-
PK, Unique, Real: 0, 0, 0
idx_SysColumn_RepoObjectColumn_via_guid__3
-
IndexSemanticGroup: no_group
-
[column-RepoObject_guid]; uniqueidentifier
-
[column-RepoObjectColumn_name]; nvarchar(128)
-
-
PK, Unique, Real: 0, 0, 0
idx_SysColumn_RepoObjectColumn_via_guid__4
-
IndexSemanticGroup: no_group
-
[column-RepoObject_guid]; uniqueidentifier
-
-
PK, Unique, Real: 0, 0, 0
Column Details
_
has_different_sys_names
has_different_sys_names |
|
|
(CONVERT([bit],case when [RepoObjectColumn_name]<>[SysObjectColumn_name] then (1) else (0) end))
is_RepoObjectColumn_name_uniqueidentifier
is_RepoObjectColumn_name_uniqueidentifier |
|
|
(case when TRY_CAST([RepoObjectColumn_name] AS [uniqueidentifier]) IS NULL then (0) else (1) end)
is_SysObjectColumn_name_uniqueidentifier
is_SysObjectColumn_name_uniqueidentifier |
|
|
(case when TRY_CAST([SysObjectColumn_name] AS [uniqueidentifier]) IS NULL then (0) else (1) end)
persistence_source_RepoObjectColumn_guid
persistence_source_RepoObjectColumn_guid |
|
|
RepoObject_fullname
RepoObject_fullname |
|
|
(concat('[',[RepoObject_schema_name],'].[',[RepoObject_name],']'))
SysObject_fullname
SysObject_fullname |
|
|
(concat('[',[SysObject_schema_name],'].[',[SysObject_name],']'))
SysObject_RepoObjectColumn_guid
SysObject_RepoObjectColumn_guid |
|
|
sql_modules_definition
repo.SysColumn_RepoObjectColumn_via_guid - V script
CREATE View repo.SysColumn_RepoObjectColumn_via_guid
As
--
Select
sc.SysObject_id
, sc.SysObject_column_id
, ro.is_repo_managed
, ro.RepoObject_fullname
, ro.SysObject_fullname
, roc.has_different_sys_names
, roc.is_query_plan_expression
, roc.is_RepoObjectColumn_name_uniqueidentifier
, roc.is_SysObjectColumn_missing
, roc.is_SysObjectColumn_name_uniqueidentifier
, roc.persistence_source_RepoObjectColumn_guid
, roc.Referencing_Count
, roc.Repo_default_definition
, roc.Repo_default_name
, roc.Repo_default_is_system_named
, roc.Repo_definition
, roc.Repo_generated_always_type
, roc.Repo_graph_type
, roc.Repo_is_computed
, roc.Repo_is_identity
, roc.Repo_is_nullable
, roc.Repo_is_persisted
, roc.Repo_seed_value
, roc.Repo_increment_value
, roc.Repo_user_type_fullname
, roc.Repo_user_type_name
, roc.Repo_uses_database_collation
, roc.RepoObject_guid
, roc.RepoObjectColumn_guid
, roc.RepoObjectColumn_name
, roc.SysObjectColumn_column_id
, roc.SysObjectColumn_name
, sc.default_definition
, sc.default_is_system_named
, sc.default_name
, sc.definition
, sc.generated_always_type
, sc.graph_type
, sc.is_computed
, sc.is_identity
, sc.is_nullable
, sc.is_persisted
, sc.seed_value
, sc.increment_value
, sc.SysObject_column_name
, sc.SysObject_name
, sc.SysObject_RepoObject_guid
, sc.SysObject_RepoObjectColumn_guid
, sc.SysObject_schema_name
, sc.SysObject_type
, sc.user_type_fullname
, sc.user_type_name
, sc.uses_database_collation
--sometimes we have columns with same name but different guid in repo.RepoObjectColumn, additional columns should be deleted in [repo].[usp_sync_guid_RepoObjectColumn]
--here we prepare, which to keep (RowNumberOverName = 1)
--we should keep RepoObjectColumn_name, because there could be PK defined and other properties
, RowNumberOverName = Row_Number () Over ( Partition By
sc.SysObject_RepoObject_guid
, sc.SysObject_column_name
Order By
roc.is_RepoObjectColumn_name_uniqueidentifier
, roc.is_SysObjectColumn_name_uniqueidentifier
)
From
repo_sys.SysColumn As sc
Left Outer Join
repo.RepoObjectColumn As roc
On
roc.RepoObjectColumn_guid = sc.SysObject_RepoObjectColumn_guid
Inner Join
repo.RepoObject As ro
On
ro.RepoObject_guid = roc.RepoObject_guid
And ro.is_ssas = 0
And ro.is_external = 0