Sql-server – How to a database parameter be used on `sp_addextendedproperty`

sql serversql server 2014stored-procedurest-sql

I have found the MSDN definition and code examples of 'sp_addextendedproperty'. In Example A on that page, I see an example of how to add an extended property to a database.

USE AdventureWorks2012;
GO
--Add a caption to the AdventureWorks2012 Database object itself.
EXEC sp_addextendedproperty 
@name = N'Caption', 
@value = 'AdventureWorks2012 Sample OLTP Database';

The thing that is immediately obvious is that you first have to declare a database context, and the stored procedure operates only within that database context.

If I wanted to add a database as a parameter, I suppose I could create a separate script that wraps around sp_addextendedproperty and sets the database context before executing the stored procedure.

For example 

-- execute wrapper stored procedure
EXEC WrapperStoredProcedure @DatabaseName = N'Database name',
    @name = N'Extended property name',
    @value = N'Extended property value';
GO

In this case WrapperStoredProcedure probably uses Dynamic SQL to set the database context and execute sp_addextendedproperty.

Am I missing an obvious technique or convention that will allow me to declare the database as part of the EXEC sp_addextendedproperty statement, without having to create a separate wrapper scripts?

Best Answer

The grammar for exec accepts a @module_name_var which allows the module name to be in a variable.

This does accepts three part names so the below does the trick.

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';

Or alternatively having just reviewed the question again you may just need this anyway.

EXEC AdventureWorks2012.sys.sp_addextendedproperty
  @name = N'Caption',
  @value = 'AdventureWorks2012 Sample OLTP Database';
Related Question