I was playing around with an idea for using extended properties to hold metadata regarding the Source Control version of an object. We manage our DB objects in Vault, and it is always difficult to verify that the DB and Vault are in sync.
My idea was to add some code to the bottom of each object creation script that took the SC expanded keyword $Revision: $ and used to it build an extended property. Then I decided to make it work in SQL 2005 for grins :)
Here is the SQL 2000 version:
IF EXISTS(SELECT * FROM dbo.sysproperties WHERE id = OBJECT_ID('Configuration') AND name = 'SCVersion')
EXEC dbo.sp_dropextendedproperty N'SCVersion', N'user', N'dbo', N'table', N'Configuration'
DECLARE @v NVARCHAR(20); SET @v = CAST(REPLACE(REPLACE('$Revision: 1 $', 'Revision:', ''), '$', '') AS INT)
EXEC dbo.sp_addextendedproperty N'SCVersion', @v, N'user', N'dbo', N'table', N'Configuration'
-- SELECT * FROM dbo.sysproperties WHERE id = OBJECT_ID('Configuration') AND name = 'SCVersion' |
For SQL 2005:
IF EXISTS(SELECT * FROM sys.extended_properties WHERE major_id = OBJECT_ID('Configuration') AND name = 'SCVersion')
EXEC dbo.sp_dropextendedproperty N'SCVersion', N'user', N'dbo', N'table', N'Configuration'
DECLARE @v NVARCHAR(20); SET @v = REPLACE(REPLACE('$Revision: 1 $', 'Revision:', ''), '$', '')
EXEC dbo.sp_addextendedproperty N'SCVersion', @v, N'user', N'dbo', N'table', N'Configuration'
-- SELECT * FROM sys.extended_properties WHERE major_id = OBJECT_ID('Configuration') AND name = 'SCVersion' |