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

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

Filed under: Documentation, Microsoft SQL Server, SQLServerPedia Syndication, System Functions and Stored Procedures Tagged: Documentation, microsoft sql server, system functions