repo_sys.ExtendedProperties - V

/*
database_id required in
- OBJECT_SCHEMA_NAME
- OBJECT_NAME
*/
CREATE View repo_sys.ExtendedProperties
As
--
Select
    sep.class
  , sep.major_id
  , sep.minor_id
  , property_name         = sep.name Collate Database_Default
  , sep.class_desc
  , property_value        = sep.value
  , SysObject_schema_name = Case
                                When sep.class In
                                ( 1, 2, 7 )
                                    Then
                                    Object_Schema_Name ( sep.major_id, db.dwh_database_id )
                                When sep.class = 3
                                    Then
                                    sch.name
                            End Collate Database_Default
  , SysObject_name        = Case
                                When sep.class In
                                ( 1, 2, 7 )
                                    Then
                                    Object_Name ( sep.major_id, db.dwh_database_id )
                            End
  , minor_name            = Case sep.class
                                When 1
                                    Then
                                    sc.name
                                When 2
                                    Then
                                    sp.name
                                When 3
                                    Then
                                    si.name
                            End Collate Database_Default
  , entity_column_name    = Case
                                When sep.class = 1
                                    Then
                                    sc.name
                            End Collate Database_Default
  , entity_parameter_name = Case
                                When sep.class = 2
                                    Then
                                    sp.name
                            End Collate Database_Default
  , entity_index_name     = Case
                                When sep.class = 7
                                    Then
                                    si.name
                            End Collate Database_Default
  , level2type            = Case
                                When sep.class = 1
                                     And sep.minor_id > 0
                                    Then
                                    'COLUMN'
                                When sep.class = 2
                                     And sep.minor_id > 0
                                    Then
                                    'PARAMETER'
                                When sep.class = 7
                                     And sep.minor_id > 0
                                    Then
                                    'INDEX'
                            End
  , property_basetype     = Sql_Variant_Property ( sep.value, 'BaseType' )
  , property_nvarchar     = Try_Cast(sep.value As NVarchar(4000))
  , so.parent_object_id
  , parent_name           = parent.name
  , parent_type           = parent.type
-- Explicit conversion from data type int to uniqueidentifier is not allowed.
--, [property_value_uniqueidentifier] = TRY_CAST([sep].value As UniqueIdentifier)
From
    sys_dwh.extended_properties            As sep
    Left Outer Join
        sys_dwh.columns                    As sc
            On
            sep.major_id = sc.object_id
            And sep.minor_id = sc.column_id

    Left Outer Join
        sys_dwh.parameters                 As sp
            On
            sep.major_id = sp.object_id
            And sep.minor_id = sp.parameter_id

    Left Outer Join
        sys_dwh.indexes                    As si
            On
            sep.major_id = si.object_id
            And sep.minor_id = si.index_id

    Left Outer Join
        sys_dwh.objects                    As so
            On
            sep.major_id = so.object_id

    Left Outer Join
        sys_dwh.objects                    As parent
            On
            parent.object_id = so.parent_object_id

    Left Outer Join
        sys_dwh.schemas                    As sch
            On
            sch.schema_id = sep.major_id
            And sep.minor_id = 0
            And sep.class = 3
    --
    Cross Apply config.ftv_dwh_database () As db