0

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.

divided
  • 1,249
  • 5
  • 14
  • 27
  • 1
    What are you trying to accomplish by storing metadata like this? There is probably a better way. – JNK Aug 25 '11 at 15:02
  • We are storing a unique table identifier in the metadata, basically so that we know what the table contains (Data from February 2011, for example). – divided Aug 25 '11 at 15:20
  • yikes, you are adding/subtracting/updating tables that quickly? – JNK Aug 25 '11 at 15:21
  • No, it's used when we create a new table so we know what that table contains. – divided Aug 25 '11 at 15:26

1 Answers1

2

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.

gbn
  • 422,506
  • 82
  • 585
  • 676
  • Instead of wrapping it in a proc, could I just call sp_addextendedproperty(@name,@value)? – divided Aug 25 '11 at 15:28
  • @divided: this requires enough rights to drop and create objects. This is a no-no in many shops or if you are subject to SOX/PCI etc – gbn Aug 25 '11 at 15:29