repo_sys.usp_AddOrUpdateExtendedProperty - P

type: P ( stored procedure ), modify_date: 2021-08-11 13:00:12

RepoObject_guid: A590291C-9D61-EB11-84DC-A81E8446D5B0

Description

Examples

Parameters

  • @name (sysname)

  • @value (sql_variant)

  • @level0type (varchar(128))

  • @level0name (sysname)

  • @level1type (varchar(128))

  • @level1name (sysname)

  • @level2type (varchar(128))

  • @level2name (sysname)

Entity Diagram

entity-repo_sys.usp_addorupdateextendedproperty

References

Object Reference Diagram - 1 1

entity_1_1_objectref-repo_sys.usp_addorupdateextendedproperty

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-repo_sys.usp_addorupdateextendedproperty

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-repo_sys.usp_addorupdateextendedproperty

sql_modules_definition

repo_sys.usp_AddOrUpdateExtendedProperty - P script
/*
EXEC repo_sys.sp_AddOrUpdateExtendedProperty
    @name = N'repo_guid'
    ,@value = N'Employee ID'
    ,@level0type = N'Schema', @level0name = dbo
    ,@level1type = N'Table',  @level1name = T1
    ,@level2type = N'Column', @level2name = id;


sysnonym will not work because sp_updateextendedproperty and sp_addextendedproperty will always use the current datebase context

https://dba.stackexchange.com/questions/136135/how-can-a-database-parameter-be-used-on-sp-addextendedproperty

DECLARE @DbName SYSNAME = 'AdventureWorks2012';
DECLARE @module_name_var NVARCHAR(500) = QUOTENAME(@DbName) +
                                              '.sys.sp_addextendedproperty';

EXEC @module_name_var
  @name = N'Caption',
  @value = 'AdventureWorks2012 Sample OLTP Database';

https://docs.microsoft.com/de-de/sql/t-sql/language-elements/execute-transact-sql?view=sql-server-ver15

module_name

Is the fully qualified or nonfully qualified name of the stored procedure or scalar-valued user-defined function to call.
Module names must comply with the rules for identifiers.
The names of extended stored procedures are always case-sensitive, regardless of the collation of the server.

A module that has been created in another database can be executed if the user running the module owns the module or has the appropriate permission to execute it in that database.
A module can be executed on another server running SQL Server if the user running the module has the appropriate permission to use that server (remote access) and to execute the module in that database.
If a server name is specified but no database name is specified, the SQL Server Database Engine looks for the module in the default database of the user.

@module_name_var

Is the name of a locally defined variable that represents a module name.

This can be a variable that holds the name of a natively compiled, scalar user-defined function.


immer noch Fehler:

RepoObject_guid;92D613F2-5752-EB11-84D5-A81E8446D5B0;Schema;Warehouse;TABLE;ColdRoomTemperatures;;;
Msg 12320, Level 16, State 80, Procedure WideWorldImporters-test.sys.sp_addextendedproperty, Line 37 [Batch Start Line 2]
Operations that require a change to the schema version, for example renaming, are not supported with memory optimized tables.

=> todo
*/
CREATE Procedure repo_sys.[usp_AddOrUpdateExtendedProperty]
    @name       sysname
  , @value      Sql_Variant  = Null
  , @level0type Varchar(128) = Null
  , @level0name sysname      = Null
  , @level1type Varchar(128) = Null
  , @level1name sysname      = Null
  , @level2type Varchar(128) = Null
  , @level2name sysname      = Null
As
Declare @DbName sysname = [config].fs_dwh_database_name ();
Declare
    @module_name_var_update NVarchar(500) = QuoteName ( @DbName ) + N'.sys.sp_updateextendedproperty'
  , @module_name_var_add    NVarchar(500) = QuoteName ( @DbName ) + N'.sys.sp_addextendedproperty';

----DEBUG
--PRINT CONCAT(@name , ';' , CAST(@value AS NVARCHAR(4000)) , ';' , @level0type , ';' , @level0name , ';' , @level1type , ';' , @level1name , ';' , @level2type , ';' , @level2name , ';')
----DEBUG
--
Begin Try
    --EXEC [sys].sp_updateextendedproperty
    Exec @module_name_var_update
        @name = @name
      , @value = @value
      , @level0type = @level0type
      , @level0name = @level0name
      , @level1type = @level1type
      , @level1name = @level1name
      , @level2type = @level2type
      , @level2name = @level2name;
End Try
Begin Catch
    Begin Try
        --EXEC [sys].sp_addextendedproperty
        Exec @module_name_var_add
            @name = @name
          , @value = @value
          , @level0type = @level0type
          , @level0name = @level0name
          , @level1type = @level1type
          , @level1name = @level1name
          , @level2type = @level2type
          , @level2name = @level2name;
    End Try
    Begin Catch
        Print 'Can''t insert extended property:';
        Print Concat (
                         @name
                       , ';'
                       , Cast(@value As NVarchar(4000))
                       , ';'
                       , @level0type
                       , ';'
                       , @level0name
                       , ';'
                       , @level1type
                       , ';'
                       , @level1name
                       , ';'
                       , @level2type
                       , ';'
                       , @level2name
                       , ';'
                     );
    End Catch;
End Catch;