Maintaining version number metadata in your SQL Database

When working with a SQL server database you may be in doubt as to what version etc you are using in your development, test, production. I suggest the use of SQL extended properties (though you might also just have a “Version” table) so for instance your tests always run on the expected data-platform! Here are the T-SQL commands to make use of the extended properties on the database level, though you can also use SMO:

EXEC sp_addextendedproperty
@name = 'Version',
@Value = '01.01'

SELECT name, value
FROM sys.extended_properties
WHERE class = 0

EXEC sp_updateextendedproperty
@name = 'Version',
@Value = '01.02'

SELECT name, value
FROM sys.extended_properties
WHERE class = 0

EXEC sp_dropextendedproperty
@name = 'Version'
Advertisements
Posted in SQL