sqlsql-servert-sqlsql-server-2008adhoc-queries

Updating table metadata on the fly

We put a unique identifier in the extended properties of each table, which is then read by some reports. I'd like to be able to do this on the fly, so we don't have to type it in by hand. However, SQL Server 2008 does not allow ad hoc updates to system catalogs.

How can I update the extended properties with an ad hoc query? Or, is there another place that I can store metadata where it can be easily read by reports?

Any help is greatly appreciated.

Solution

  • One option:

    Extended properties are added using sp_addextendedproperty (there are update and drop procs too). These require enhanced rights though.

    So you can wrap a calls to this in another proc with EXECUTE AS to escalate rights for the stored proc only.

    CREATE PROC dbo.OurMetaDataUpdate
   @tablename ...
   @uniqueid ...
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON

EXEC sp_addextendedproperty ...
GO

    Either that, or use your own table to maintain this.