Extending your metadata with Extended Properties

9

May 18, 2015 by Kenneth Fisher

Ever wanted to put a comment on a table? Or maybe even a column? How about an expiration date on a object? Well as it happens you can. Extended properties allow you to add a name/value property to a number of the objects in SQL Server. In fact if you spend any time looking at property pages of various objects you will see the last entry is typically Extended Properties.

I’ll be honest, in the 20+ years of my career I’ve used extended properties maybe twice. But as I write this post I’m starting to rethink that in a very specific case. And I can think of several other cases where it would be highly useful.

  • Put a TBD (to be deleted) date on “backup” objects.
    I discussed operational recovery a while back, and one of the things I mentioned was making copies of the old SP before you created a new one. Or creating a new version of a table with the current data before you do a major update. Well wouldn’t it be nice to put a TBD/date pair in the extended properties of those objects, and then have an automated process review them and actually delete them once that date has passed?
  • Comments on schemas, tables or columns.
    Comments on SPs, functions etc are common and easy enough to do. They can let you know what changes have been made and why, what the purpose of the code is etc. Well with extended properties you can do the same thing with a table. You can add an extended property to let you know what the Delegate schema is for. Why has no one ever fixed the misspelling on the cheldrin table. Etc.
  • Adding description and owner values for SQL Server logins
    This is the use case I was talking about earlier. If you work with legacy systems, network systems that have connections in from areas that don’t allow windows logins, etc then you probably have mixed authentication and SQL Server logins for various applications. If you are unlucky enough you might even have a SQL Server login called App_Id. Imagine having an extended property with the purpose of the account and the name of the team that requested it? The down side is that you can only use extended properties on database principals not server principals. So if your application id is going to be used on more than one database you should probably document it on each database that it’s used.

 
 
Example:

-- Create the Login and User
CREATE LOGIN MakeNotes WITH PASSWORD = 'T@st1'
GO
USE Test
GO
CREATE USER MakeNotes FROM LOGIN MakeNotes
GO
-- Add the extended properties by code
EXEC sys.sp_addextendedproperty @name=N'Description', @value=N'This user is for my example' , @level0type=N'USER',@level0name=N'MakeNotes'
GO
EXEC sys.sp_addextendedproperty @name=N'Owner', @value=N'DBA Team' , @level0type=N'USER',@level0name=N'MakeNotes'
GO

Add extended properties by GUI
ExtendedProperties1

-- View the extended properties
SELECT ep.*, dp.name FROM sys.extended_properties ep
JOIN sys.database_principals dp
	ON ep.major_id = dp.principal_id
WHERE class_desc = 'DATABASE_PRINCIPAL'
GO

9 thoughts on “Extending your metadata with Extended Properties

  1. notarian says:

    Chris Bell has an interesting use case for Extended Properties. Have DDL triggers write to them so you have proof that people are messing with your schema.

  2. davidbainbridge83 says:

    Used them in the past for creating a data dictionary on the fly. It is easy enough to get a list of tables, columns etc but eps were useful for storing the what.

    We tied this to a report which would produce an always up to date data dictionary (assuming the eps were up to date of course).

  3. […] Visual Studio Online in SSMS Thoughts On All The Recent Power BI/SQL Server 2016 BI/Excel 2016 News Extending your metadata with Extended Properties Documenting your SQL Server Database Continuous delivery and Azure SQL Database achieving the […]

  4. […] a problem) you have no idea who created it or how it gets populated. I recently stumbled across an article by Kenneth Fisher about using extended properties to handle meta data, so I took the idea a little […]

  5. Charles Kincaid says:

    You can put description extended properties on just about everything in SQL Server. Unfortunately doing so is often a pain. SSMS only supports extended property dialogs on a fraction of the total.

    I have written a stand alone tool the makes doing this job simple and it lets you put a description on anything where an extended property is allowed. The interface is the same regardless of where you are putting the description.

    I was planning on a release in April but had a set back. I’m planning on either June or July for the open beta month. Currently workimng out business and legal issues. Starting a new company is the hard part.

  6. […] you find out the strangest things about SQL Server. I’ve written about extended properties before but had no idea they actually did anything beyond documentation. Then the other day I was […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,646 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: