repo_sys.parameters - V

type: V ( view ), modify_date: 2021-08-17 20:01:47

RepoObject_guid: 9CDFBE88-CA97-EB11-84F4-A81E8446D5B0

Description

Examples

Entity Diagram

entity-repo_sys.parameters

Columns

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

sysname

NULL

int

NULL

sql_variant

NULL

sysname

NULL

int

NULL

nvarchar(64)

NULL

bit

NOT NULL

bit

NOT NULL

bit

NULL

bit

NOT NULL

bit

NOT NULL

bit

NOT NULL

smallint

NOT NULL

sysname

NULL

int

NOT NULL

int

NOT NULL

tinyint

NOT NULL

tinyint

NOT NULL

nvarchar(517)

NULL

nvarchar(257)

NULL

sysname

NULL

uniqueidentifier

NULL

sysname

NULL

char(2)

NULL

tinyint

NOT NULL

nvarchar(182)

NULL

int

NOT NULL

sysname

NULL

int

NOT NULL

Foreign Key Diagram

entity_1_1_fk-repo_sys.parameters

References

Referencing Objects

Object Reference Diagram - 1 1

entity_1_1_objectref-repo_sys.parameters

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-repo_sys.parameters

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-repo_sys.parameters

Column Reference Diagram

entity_1_1_colref-repo_sys.parameters

Column Details

_

column_encryption_key_database_name

column_encryption_key_database_name

sysname

NULL

column_encryption_key_id

column_encryption_key_id

int

NULL

default_value

default_value

sql_variant

NULL

encryption_algorithm_name

encryption_algorithm_name

sysname

NULL

encryption_type

encryption_type

int

NULL

encryption_type_desc

encryption_type_desc

nvarchar(64)

NULL

has_default_value

has_default_value

bit

NOT NULL

is_cursor_ref

is_cursor_ref

bit

NOT NULL

is_nullable

is_nullable

bit

NULL

is_output

is_output

bit

NOT NULL

is_readonly

is_readonly

bit

NOT NULL

is_xml_document

is_xml_document

bit

NOT NULL

max_length

max_length

smallint

NOT NULL

name

name

sysname

NULL

object_id

object_id

int

NOT NULL

parameter_id

parameter_id

int

NOT NULL

precision

precision

tinyint

NOT NULL

scale

scale

tinyint

NOT NULL

SysObject_fullname

SysObject_fullname

nvarchar(517)

NULL

SysObject_fullname2

SysObject_fullname2

nvarchar(257)

NULL

SysObject_name

SysObject_name

sysname

NULL

SysObject_RepoObject_guid

SysObject_RepoObject_guid

uniqueidentifier

NULL

SysObject_schema_name

SysObject_schema_name

sysname

NULL

SysObject_type

SysObject_type

char(2)

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

user_type_name

user_type_name

sysname

NULL

xml_collection_id

xml_collection_id

int

NOT NULL

sql_modules_definition

repo_sys.parameters - V script
CREATE View repo_sys.parameters
As
Select
    par.object_id
  , par.name
  , par.parameter_id
  , par.system_type_id
  , par.user_type_id
  , par.max_length
  , par.precision
  , par.scale
  , par.is_output
  , par.is_cursor_ref
  , par.has_default_value
  , par.is_xml_document
  , par.default_value
  , par.xml_collection_id
  , par.is_readonly
  , par.is_nullable
  , par.encryption_type
  , par.encryption_type_desc
  , par.encryption_algorithm_name
  , par.column_encryption_key_id
  , par.column_encryption_key_database_name
  -- 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(par.max_length = -1, 'max', Cast(par.max_length As Varchar(25)))
                                 + ')'
                             When tp.name In
                             ( 'nvarchar', 'nchar' )
                                 Then
                                 tp.name + '('
                                 + Iif(par.max_length = -1, 'max', Cast(par.max_length / 2 As Varchar(25))) + ')'
                             When tp.name In
                             ( 'decimal', 'numeric' )
                                 Then
                                 tp.name + '(' + Cast(par.precision As Varchar(25)) + ', '
                                 + Cast(par.scale As Varchar(25)) + ')'
                             When tp.name In
                             ( 'datetime2' )
                                 Then
                                 tp.name + '(' + Cast(par.scale As Varchar(25)) + ')'
                             Else
                                 tp.name
                         End Collate Database_Default
  , so.SysObject_fullname
  , so.SysObject_fullname2
  , so.SysObject_name
  , so.SysObject_RepoObject_guid
  , so.SysObject_schema_name
  , SysObject_type     = so.type
From
    sys_dwh.parameters     As par
    Left Outer Join
        sys_dwh.types      As tp
            On
            tp.user_type_id = par.user_type_id

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