T-SQL: Version your database with Extended Properties

Recently i've had a requirement to version a database build in a simliar way to AdventureWorks which uses a version table called AWBuildVersion. Of course, that is a perfectly acceptable option but as with all things SQL, there is more than one way to skin a cat. Extended properties are not a new feature to SQL Server but they might as well be for the number of times i've used them but the thought came to me that they could well be suitable for keeping version properties of a database.

Here is the code to generate the same database version info that is kept in the AdventureWorks build table.

-- add a version to your database
EXECUTE sys.sp_addextendedproperty @name = N'SystemInformationID', @value = N'1';
EXECUTE sys.sp_addextendedproperty @name = N'Database Version', @value = N'10.00.80404.00';
EXECUTE sys.sp_addextendedproperty @name = N'VersionDate', @value = N'2008-04-04';
EXECUTE sys.sp_addextendedproperty @name = N'ModifiedDate', @value = N'2008-04-04';

-- read out the version
SELECT *
FROM fn_listextendedproperty (NULL, NULL, NULL, NULL, NULL, NULL, NULL);

This returns:


Will I use this going forward? The thing with extended properties is that they aren't a widely used feature and having the data in a table makes it much more accessible. As a result, its much easier to remember the syntax to update a database table than an extended property. Also, a database table is more "visible" so is less likely to be forgotten when you come to update the version.