sqlparse.RepoObject_SqlModules_22_identifier_alias_AS - V

type: V ( view ), modify_date: 2021-08-17 20:06:33

RepoObject_guid: 3790291C-9D61-EB11-84DC-A81E8446D5B0

Description

Examples

Entity Diagram

entity-sqlparse.repoobject_sqlmodules_22_identifier_alias_as

Columns

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

1

uniqueidentifier

NOT NULL

2

nvarchar(4000)

NOT NULL

3

bigint

NULL

nvarchar(max)

NULL

nvarchar(max)

NULL

nvarchar(261)

NULL

Foreign Key Diagram

entity_1_1_fk-sqlparse.repoobject_sqlmodules_22_identifier_alias_as

References

Object Reference Diagram - 1 1

entity_1_1_objectref-sqlparse.repoobject_sqlmodules_22_identifier_alias_as

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-sqlparse.repoobject_sqlmodules_22_identifier_alias_as

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-sqlparse.repoobject_sqlmodules_22_identifier_alias_as

Column Reference Diagram

entity_1_1_colref-sqlparse.repoobject_sqlmodules_22_identifier_alias_as

Indexes

PK_RepoObject_SqlModules_22_identifier_alias_AS

idx_RepoObject_SqlModules_22_identifier_alias_AS__2

idx_RepoObject_SqlModules_22_identifier_alias_AS__3

Column Details

_

RepoObject_guid

1

RepoObject_guid

uniqueidentifier

NOT NULL

json_key

2

json_key

nvarchar(4000)

NOT NULL

RowNumber

3

RowNumber

bigint

NULL

identifier_alias

identifier_alias

nvarchar(max)

NULL

identifier_name

identifier_name

nvarchar(max)

NULL

SysObject_fullname

SysObject_fullname

nvarchar(261)

NULL

Description

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


sql_modules_definition

sqlparse.RepoObject_SqlModules_22_identifier_alias_AS - V script
/*
this works good for identifier, used in the FROM block of a statement:
it looks like these identifiers have only one child and inside this one child only one child identifier which is the alias
we also add a Row_number to enable filtering for the first alias (there should only be one, but who knows...)
by filtering ([RowNumber] = 1) in some next steps we ensure one entry per ([RepoObject_guid], [key])

But we have also a lot of other representations of tables, especially because of the (NOLOCK)
and we need some extra handling to extract them
*/
CREATE View sqlparse.RepoObject_SqlModules_22_identifier_alias_AS
As
--
Select
    T1.RepoObject_guid
  , T1.json_key
  , T1.SysObject_fullname
  , identifier_name  = T1.normalized
  , identifier_alias = T2.normalized
  , RowNumber        = Row_Number () Over ( Partition By T1.RepoObject_guid, T1.json_key Order By T2.json_key )
From
    sqlparse.RepoObject_SqlModules_20_statement_children As T1
    Cross Apply sqlparse.ftv_sqlparse ( T1.children )    As T2
Where
    T1.class     = 'Identifier'
    And T2.class = 'Identifier'