Monday, April 25, 2011

How to Hide Metadata Information Using Extended Properties

Ok, a more honest title for this post might be, 'How to Clean Up the Mess After You've Hosed the Extended Properties on Primary Key Constraints', but that wouldn't fit.

What I was trying to accomplish is a topic for another post. But while grumbling loudly while writing the code to fix what I had inadvertently done, I realized it could be used to "hide" information about tables. I say "hide", in quotes, because anyone with access to Books Online could find it easily. This is really just a way of storing information about tables, and not having it appear in SQL Server Management Studio (SSMS).

What I ran into involves the difference between primary key constraints and primary key indexes. The most important distinction between them is "existence"; there's no such thing as a primary key index. If you're confused, keep reading. If you've heard this story already, skip ahead a bit. Simply put, when someone is talking about "primary key indexes", they're using verbal shorthand for, "the index that the primary key constraint uses to enforce uniqueness." (As far as I know, this kind of index has no specific name, which is good, because if if did, we'd all confuse it with "primary key constraint", so we'd be in the same boat we are now, but without the ability to abruptly clarify things by saying, "but there's no such thing as a whatever-it-is." We should count our blessings.) To sum up, when it comes to primary keys, we have a "primary key constraint", and a unique index of some sort to help it. (We won't be getting into the whole clustered vs. non-clustered thing here.)

Now we're ready to talk about setting extended properties on primary key constraints. First we create a little test table:

    MyPkColumn INT NOT NULL,

And now we add an extended property to the PK_MyTable constraint, as so:

EXEC sys.sp_addextendedproperty 
    @name       = N'Extended property for PK_MyTable constraint'
  , @value      = N'This is the PK for MyTable'
  , @level0type = N'SCHEMA'
  , @level0name = N'dbo'
  , @level1type = N'TABLE'
  , @level1name = N'MyTable'
  , @level2type = N'CONSTRAINT'
  , @level2name = N'PK_MyTable'

Once added, this extended property will appear in SSMS, as expected. Oddly, it appears on the index-the-primary-key-constraint-uses, not on the constraint itself. (And no, I'm not suggesting you create tables in the master database.) So far, so good. But given that the "slot" for the index is already taken, and knowing what we do about about the non-existence of "primary key indexes", what happens if we do this?

EXEC sys.sp_addextendedproperty 
    @name       = N'Secret extended property for PK_MyTable index'
  , @value      = N'This is the PK index for MyTable'
  , @level0type = N'SCHEMA'
  , @level0name = N'dbo'
  , @level1type = N'TABLE'
  , @level1name = N'MyTable'
  , @level2type = N'INDEX'
  , @level2name = N'PK_MyTable'

Ideally (I think anyway) this should generate an error: we're adding an extended property to a CONSTRAINT, but setting @level2name to INDEX. What it actually does is create the extended property as if there really were such a thing as a "primary key index". Interestingly, but perhaps not surprisingly, this new extended property doesn't show up (at least not anywhere I can find) in SSMS. So how do we know it's there? We use the system views, of course!

-- Emit code to drop all extended properties on "primary key indexes". 

SELECT ep.major_id
     , ep.minor_id
     ,           AS 'Schema Name'
     ,           AS 'Table Name'
     ,           AS 'Index Name'
     ,          AS 'EP Name'
     , ep.value         AS 'EP Value'
     , 'USE ' + DB_NAME() + '   ' +
       'EXEC sys.sp_dropextendedproperty '
            + '@name = '                                 +  '''' +  + ''', '
            + '@level0type = ''SCHEMA'', @level0name = ' + quotename( + ', ' 
            + '@level1type = ''TABLE'',  @level1name = ' + quotename( + ', '
            + '@level2type = ''INDEX'',  @level2name = ' + quotename(   AS 'T-SQL'

  FROM sys.extended_properties    ep
  JOIN sys.indexes                i
    ON ep.major_id = i.object_id
   AND ep.minor_id = i.index_id
  JOIN sys.tables                 t
    ON i.object_id = t.object_id
  JOIN sys.schemas                s
    ON t.schema_id = s.schema_id
 WHERE ep.class_desc    = 'INDEX'
   AND i.is_primary_key = 1

The above displays the offending extended properties on the current database; the last column contains the code necessary to drop each one; for example:

EXEC sys.sp_dropextendedproperty 
    @name       = 'Secret extended property for PK_MyTable index', 
    @level0type = 'SCHEMA', 
    @level0name = [dbo], 
    @level1type = 'TABLE',  
    @level1name = [MyTable], 
    @level2type = 'INDEX',  
    @level2name = [PK_MyTable]

Additional Reading


Post a Comment