sqlparse.RepoObject_SqlModules_72_reference_NoTableAlias - V

type: V ( view ), modify_date: 2022-01-05 18:06:34

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

Description

Examples

Entity Diagram

entity-sqlparse.repoobject_sqlmodules_72_reference_notablealias

Columns

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

nvarchar(max)

NULL

nvarchar(max)

NULL

uniqueidentifier

NOT NULL

uniqueidentifier

NOT NULL

nvarchar(128)

NOT NULL

nvarchar(max)

NULL

uniqueidentifier

NOT NULL

uniqueidentifier

NOT NULL

nvarchar(128)

NOT NULL

nvarchar(261)

NOT NULL

nvarchar(max)

NULL

nvarchar(261)

NULL

Foreign Key Diagram

entity_1_1_fk-sqlparse.repoobject_sqlmodules_72_reference_notablealias

References

Object Reference Diagram - 1 1

entity_1_1_objectref-sqlparse.repoobject_sqlmodules_72_reference_notablealias

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-sqlparse.repoobject_sqlmodules_72_reference_notablealias

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-sqlparse.repoobject_sqlmodules_72_reference_notablealias

Column Reference Diagram

entity_1_1_colref-sqlparse.repoobject_sqlmodules_72_reference_notablealias

Indexes

idx_RepoObject_SqlModules_72_reference_NoTableAlias__1

idx_RepoObject_SqlModules_72_reference_NoTableAlias__2

idx_RepoObject_SqlModules_72_reference_NoTableAlias__3

idx_RepoObject_SqlModules_72_reference_NoTableAlias__4

idx_RepoObject_SqlModules_72_reference_NoTableAlias__5

Column Details

_

alias_QuoteName

alias_QuoteName

nvarchar(max)

NULL

normalized

normalized

nvarchar(max)

NULL

RepoObject_guid

RepoObject_guid

uniqueidentifier

NOT NULL

RepoObjectColumn_guid

RepoObjectColumn_guid

uniqueidentifier

NOT NULL

RepoObjectColumn_name

RepoObjectColumn_name

nvarchar(128)

NOT NULL

source_column_QuoteName

source_column_QuoteName

nvarchar(max)

NULL

source_RepoObject_guid

source_RepoObject_guid

uniqueidentifier

NOT NULL

Referenced Columns

source_RepoObjectColumn_guid

source_RepoObjectColumn_guid

uniqueidentifier

NOT NULL

source_RepoObjectColumn_name

source_RepoObjectColumn_name

nvarchar(128)

NOT NULL

source_SysObject_fullname

source_SysObject_fullname

nvarchar(261)

NOT NULL

Description

(concat('[',[SysObject_schema_name],'].[',[SysObject_name],']'))


source_table_QuoteName

source_table_QuoteName

nvarchar(max)

NULL

SysObject_fullname

SysObject_fullname

nvarchar(261)

NULL

sql_modules_definition

sqlparse.RepoObject_SqlModules_72_reference_NoTableAlias - V script
/*
missing table alias:

T1.source_table_QuoteName is NULL and can't be linked to an exsisting T44.alias_QuoteName
we try mapping only by T1.source_column_QuoteName

for example

aaa
from
table as T1

aaa
from
table
*/
CREATE View sqlparse.RepoObject_SqlModules_72_reference_NoTableAlias
As
Select
    --
    T1.RepoObject_guid
  , T1.alias_QuoteName
  , T1.SysObject_fullname
  , roc.RepoObjectColumn_guid
  , roc.RepoObjectColumn_name
  , source_RepoObject_guid       = ro_source.RepoObject_guid
  , source_SysObject_fullname    = ro_source.SysObject_fullname
  , source_RepoObjectColumn_guid = roc_source.RepoObjectColumn_guid
  , source_RepoObjectColumn_name = roc_source.RepoObjectColumn_name
  , T1.source_table_QuoteName
  , T1.source_column_QuoteName
  , T1.normalized
From
    sqlparse.RepoObject_SqlModules_61_SelectIdentifier_Union_T      As T1
    Inner Join
        sqlparse.RepoObject_SqlModules_44_from_Identifier_QuoteName As T44
            On
            T44.RepoObject_guid                           = T1.RepoObject_guid
    -- T1.source_table_QuoteName is NULL and can't be linked to an exsisting T44.alias_QuoteName
    --AND T44.alias_QuoteName = T1.source_table_QuoteName

    Inner Join
        repo.RepoObjectColumn                                       As roc
            On
            QuoteName ( roc.SysObjectColumn_name )        = T1.alias_QuoteName
            And roc.RepoObject_guid                       = T1.RepoObject_guid

    Inner Join
        repo.RepoObject                                             As ro_source
            On
            QuoteName ( ro_source.SysObject_schema_name ) = T44.name_PreDot_QuoteName
            And QuoteName ( ro_source.SysObject_name )    = T44.name_PostDot_QuoteName

    Inner Join
        repo.RepoObjectColumn                                       As roc_source
            On
            QuoteName ( roc_source.SysObjectColumn_name ) = T1.source_column_QuoteName
            And roc_source.RepoObject_guid                = ro_source.RepoObject_guid
Where
    --can't be linked to an exsisting T44.alias_QuoteName
    T1.source_table_QuoteName Is Null