repo_sys.SysColumn - V

type: V ( view ), modify_date: 2021-08-17 20:03:38

RepoObject_guid: 5D90291C-9D61-EB11-84DC-A81E8446D5B0

Description

Examples

Entity Diagram

entity-repo_sys.syscolumn

Columns

Table 1. Columns of repo_sys.SysColumn - V
PK Column Name Data Type NULL? ID

sysname

NULL

sysname

NULL

int

NULL

nvarchar(max)

NULL

bit

NULL

sysname

NULL

int

NOT NULL

int

NULL

nvarchar(max)

NULL

sysname

NULL

int

NULL

nvarchar(64)

NULL

tinyint

NULL

nvarchar(60)

NULL

int

NULL

nvarchar(60)

NULL

sql_variant

NULL

bit

NOT NULL

bit

NULL

bit

NOT NULL

bit

NULL

bit

NOT NULL

bit

NULL

bit

NOT NULL

bit

NOT NULL

bit

NULL

bit

NULL

bit

NULL

bit

NULL

bit

NULL

bit

NOT NULL

bit

NULL

bit

NOT NULL

sql_variant

NULL

smallint

NOT NULL

tinyint

NOT NULL

int

NOT NULL

tinyint

NOT NULL

sql_variant

NULL

int

NOT NULL

sysname

NULL

nvarchar(517)

NULL

nvarchar(257)

NULL

int

NOT NULL

nvarchar(128)

NULL

uniqueidentifier

NULL

uniqueidentifier

NULL

nvarchar(128)

NULL

char(2)

NULL

nvarchar(60)

NULL

tinyint

NOT NULL

nvarchar(182)

NULL

int

NOT NULL

sysname

NULL

bit

NULL

int

NOT NULL

Foreign Key Diagram

entity_1_1_fk-repo_sys.syscolumn

References

Object Reference Diagram - 1 1

entity_1_1_objectref-repo_sys.syscolumn

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-repo_sys.syscolumn

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-repo_sys.syscolumn

Column Reference Diagram

entity_1_1_colref-repo_sys.syscolumn

Column Details

_

collation_name

collation_name

sysname

NULL

column_encryption_key_database_name

column_encryption_key_database_name

sysname

NULL

column_encryption_key_id

column_encryption_key_id

int

NULL

default_definition

default_definition

nvarchar(max)

NULL

default_object_id

default_object_id

int

NOT NULL

default_parent_column_id

default_parent_column_id

int

NULL

encryption_algorithm_name

encryption_algorithm_name

sysname

NULL

encryption_type

encryption_type

int

NULL

encryption_type_desc

encryption_type_desc

nvarchar(64)

NULL

generated_always_type_desc

generated_always_type_desc

nvarchar(60)

NULL

graph_type_desc

graph_type_desc

nvarchar(60)

NULL

is_ansi_padded

is_ansi_padded

bit

NOT NULL

is_column_set

is_column_set

bit

NULL

is_dts_replicated

is_dts_replicated

bit

NULL

is_filestream

is_filestream

bit

NOT NULL

is_hidden

is_hidden

bit

NULL

is_masked

is_masked

bit

NOT NULL

is_merge_published

is_merge_published

bit

NULL

is_non_sql_subscribed

is_non_sql_subscribed

bit

NULL

is_replicated

is_replicated

bit

NULL

is_rowguidcol

is_rowguidcol

bit

NOT NULL

is_sparse

is_sparse

bit

NULL

is_xml_document

is_xml_document

bit

NOT NULL

last_value

last_value

sql_variant

NULL

max_length

max_length

smallint

NOT NULL

precision

precision

tinyint

NOT NULL

rule_object_id

rule_object_id

int

NOT NULL

scale

scale

tinyint

NOT NULL

SysObject_fullname

SysObject_fullname

nvarchar(517)

NULL

SysObject_fullname2

SysObject_fullname2

nvarchar(257)

NULL

SysObject_type_desc

SysObject_type_desc

nvarchar(60)

NULL

Referenced Columns

system_type_id

system_type_id

tinyint

NOT NULL

user_type_fullname

user_type_fullname

nvarchar(182)

NULL

user_type_id

user_type_id

int

NOT NULL

xml_collection_id

xml_collection_id

int

NOT NULL

sql_modules_definition

repo_sys.SysColumn - V script
CREATE View repo_sys.SysColumn
As
--
Select
    SysObject_id                    = sc.object_id
  , SysObject_schema_name           = Object_Schema_Name ( sc.object_id, db.dwh_database_id )
  , SysObject_name                  = Object_Name ( sc.object_id, db.dwh_database_id )
  , SysObject_fullname              =
  --
  QuoteName ( Object_Schema_Name ( sc.object_id, db.dwh_database_id )) + '.'
  + QuoteName ( Object_Name ( sc.object_id, db.dwh_database_id ))
  , SysObject_fullname2             =
  --
  Object_Schema_Name ( sc.object_id, db.dwh_database_id ) + '.' + Object_Name ( sc.object_id, db.dwh_database_id )
  , SysObject_column_id             = sc.column_id
  , SysObject_column_name           = sc.name Collate Database_Default
  , SysObject_type                  = so.type
  , SysObject_type_desc             = so.type_desc
  , SysObject_RepoObject_guid       = Try_Cast(ep.property_value As UniqueIdentifier)
  , SysObject_RepoObjectColumn_guid = Try_Cast(ep2.property_value As UniqueIdentifier)
  , sc.system_type_id
  , sc.user_type_id
  -- code for [user_type_name]: https://stackoverflow.com/questions/9179990/where-do-i-find-sql-server-metadata-for-column-datatypes
  , user_type_name                  = tp.name Collate Database_Default
  , user_type_fullname              =
  --
  Case
      When tp.name In
      ( 'varchar', 'char', 'varbinary', 'binary' )
          Then
          tp.name + '(' + Iif(sc.max_length = -1, 'max', Cast(sc.max_length As Varchar(25))) + ')'
      When tp.name In
      ( 'nvarchar', 'nchar' )
          Then
          tp.name + '(' + Iif(sc.max_length = -1, 'max', Cast(sc.max_length / 2 As Varchar(25))) + ')'
      When tp.name In
      ( 'decimal', 'numeric' )
          Then
          tp.name + '(' + Cast(sc.precision As Varchar(25)) + ', ' + Cast(sc.scale As Varchar(25)) + ')'
      When tp.name In
      ( 'datetime2' )
          Then
          tp.name + '(' + Cast(sc.scale As Varchar(25)) + ')'
      Else
          tp.name
  End Collate Database_Default
  , sc.max_length
  , sc.precision
  , sc.scale
  , sc.collation_name
  , sc.is_nullable
  , sc.is_ansi_padded
  , sc.is_rowguidcol
  , sc.is_identity
  , sc.is_computed
  , sc.is_filestream
  , sc.is_replicated
  , sc.is_non_sql_subscribed
  , sc.is_merge_published
  , sc.is_dts_replicated
  , sc.is_xml_document
  , sc.xml_collection_id
  , sc.default_object_id
  , sc.rule_object_id
  , sc.is_sparse
  , sc.is_column_set
  , sc.generated_always_type
  , sc.generated_always_type_desc
  , sc.encryption_type
  , sc.encryption_type_desc
  , sc.encryption_algorithm_name
  , sc.column_encryption_key_id
  , sc.column_encryption_key_database_name
  , sc.is_hidden
  , sc.is_masked
  , sc.graph_type
  , sc.graph_type_desc
  , definition                      = scc.definition Collate Database_Default
  , scc.is_persisted
  , scc.uses_database_collation
  , default_definition              = sdc.definition Collate Database_Default
  , default_is_system_named         = sdc.is_system_named
  , default_name                    = sdc.name Collate Database_Default
  , default_parent_column_id        = sdc.parent_column_id
  , sic.seed_value
  , sic.increment_value
  , sic.last_value
--, [sic].[is_not_for_replication]
--, CAST(CASE
--           WHEN [sc].[name] = 'SysStartTime'
--                AND [tp].[name] = 'datetime2'
--           THEN 1
--           WHEN [sc].[name] = 'SysEndTime'
--                AND [tp].[name] = 'datetime2'
--           THEN 2
--       END AS TINYINT) AS                                        [temporal_column_type]
From
    sys_dwh.columns                        As sc
    Left Outer Join
        repo_sys.ExtendedProperties        As ep
            On
            ep.major_id = sc.object_id
            And ep.minor_id = 0
            And ep.property_name = N'RepoObject_guid'

    Left Outer Join
        repo_sys.ExtendedProperties        As ep2
            On
            ep2.major_id = sc.object_id
            And ep2.minor_id = sc.column_id
            And ep2.property_name = N'RepoObjectColumn_guid'

    Left Outer Join
        repo_sys.SysObject                 As so
            On
            so.SysObject_id = sc.object_id

    Left Outer Join
        sys_dwh.types                      As tp
            On
            sc.user_type_id = tp.user_type_id

    Left Outer Join
        sys_dwh.computed_columns           As scc
            On
            scc.object_id = sc.object_id
            And scc.column_id = sc.column_id

    Left Outer Join
        sys_dwh.default_constraints        As sdc
            On
            sc.default_object_id = sdc.object_id

    Left Outer Join
        sys_dwh.identity_columns           As sic
            On
            sic.object_id = sc.object_id
            And sic.column_id = sc.column_id
    --
    Cross Apply config.ftv_dwh_database () As db
Where
    Object_Schema_Name ( sc.object_id, db.dwh_database_id ) <> 'sys'