SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Extending your metadata with Extended Properties

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

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

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...