sqlparse.RepoObject_SqlModules_61_SelectIdentifier_Union - V

type: V ( view ), modify_date: 2021-08-17 20:15:30

RepoObject_guid: 127E4BE1-8F64-EB11-84DD-A81E8446D5B0

Description

Examples

Entity Diagram

entity-sqlparse.repoobject_sqlmodules_61_selectidentifier_union

Columns

Table 1. Columns of sqlparse.RepoObject_SqlModules_61_SelectIdentifier_Union - V
PK Column Name Data Type NULL? ID

nvarchar(max)

NULL

nvarchar(500)

NULL

nvarchar(max)

NULL

uniqueidentifier

NOT NULL

bigint

NULL

nvarchar(max)

NULL

nvarchar(max)

NULL

nvarchar(261)

NULL

Foreign Key Diagram

entity_1_1_fk-sqlparse.repoobject_sqlmodules_61_selectidentifier_union

References

Object Reference Diagram - 1 1

entity_1_1_objectref-sqlparse.repoobject_sqlmodules_61_selectidentifier_union

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-sqlparse.repoobject_sqlmodules_61_selectidentifier_union

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-sqlparse.repoobject_sqlmodules_61_selectidentifier_union

Column Reference Diagram

entity_1_1_colref-sqlparse.repoobject_sqlmodules_61_selectidentifier_union

Column Details

_

alias_QuoteName

alias_QuoteName

nvarchar(max)

NULL

class

class

nvarchar(500)

NULL

normalized

normalized

nvarchar(max)

NULL

RepoObject_guid

RepoObject_guid

uniqueidentifier

NOT NULL

RowNumber_per_Object

RowNumber_per_Object

bigint

NULL

source_column_QuoteName

source_column_QuoteName

nvarchar(max)

NULL

source_table_QuoteName

source_table_QuoteName

nvarchar(max)

NULL

SysObject_fullname

SysObject_fullname

nvarchar(261)

NULL

sql_modules_definition

sqlparse.RepoObject_SqlModules_61_SelectIdentifier_Union - V script
CREATE View sqlparse.RepoObject_SqlModules_61_SelectIdentifier_Union
As
Select
    T1.RepoObject_guid
  , T1.SysObject_fullname
  --can be empty, this is fine in case of only one source table in FROM, but it could be also OK in case of unique name within multiple source tables
  , T1.source_table_QuoteName
  , T1.source_column_QuoteName
  , alias_QuoteName = T1.source_column_QuoteName
  , T1.RowNumber_per_Object
  , T1.class
  , T1.normalized
From
    sqlparse.RepoObject_SqlModules_52_Identitfier_QuoteName As T1
    --only SELECT Identifier before FROM
    Inner Join
        sqlparse.RepoObject_SqlModules_39_object            As T39
            On
            T39.RepoObject_guid        = T1.RepoObject_guid
            And T39.Min_RowNumber_From = T1.RowNumber_per_Object + 1
Where
    Not T1.source_column_QuoteName Is Null
Union All
Select
    T26.RepoObject_guid
  , T26.SysObject_fullname
  --can be empty, this is fine in case of only one source table in FROM, but it could be also OK in case of unique name within multiple source tables
  , source_table_QuoteName  = T26.Identifier_source_table_QuoteName
  , source_column_QuoteName = T26.Identifier_source_column_QuoteName
  , alias_QuoteName         = T26.Identifier_alias_QuoteName
  , T26.RowNumber_per_Object
  , T26.class
  , T26.normalized
From
    sqlparse.RepoObject_SqlModules_26_IdentifierList_children_IdentifierSplit_QuoteName As T26
    Inner Join
        sqlparse.RepoObject_SqlModules_39_object                                        As T39
            On
            T26.RepoObject_guid          = T39.RepoObject_guid
            --only default views where SELECT is the 5th element in view definition
            And T39.is_5_select          = 1
            --only SELECT IdentifierList after SELECT (5)
            And T26.RowNumber_per_Object > 5
            --only SELECT IdentifierList before FROM
            And T39.Min_RowNumber_From   > T26.RowNumber_per_Object
--source column should exist (it will not exist in case of calculations, functions, ...)
Where
    Not T26.Identifier_source_column_QuoteName Is Null