How can you quickly check which build version a SQL object in your database was created/last altered in?
How can you correlate changes to individual SQL objects with the overall application build/deployment?
A previous post has shown how to include a build version with the overall database schema.
This blog will look at:
- How this concept can also be used for individual SQL objects.
- How extended properties in MS SQL Server can be used to help with versioning.
- A build version can be auto generated/incremented by build tools such as Nant.
- Each create/alter script has a token in it. On each build this is replaced with the auto generated version number .
- Extended properties can be used to store metadata about individual SQL objects.
- A sproc is provided that makes it easier to insert/update extended properties.
- Each create/alter script also includes a section where the build version extended property is inserted/updated.
Using a build tool to auto generate the build version
See this section in previous post.
Nant token replacement
See this section in previous post.
How it works
All create/alter scripts have a section similar to that shown below – this example is for a sproc:
CREATE Procedure dbo.p_SprocName AS /********************************************* * PURPOSE: See sys.extended_properties * LAST CHANGED IN BUILD: @BUILD_VERSION_NUMBER@ *********************************************/
A token @BUILD_VERSION_NUMBER@ is included in the comments section.
During a build this is replaced with an auto generated build number.
For example, to something like the following:
/********************************************* * PURPOSE: See sys.extended_properties * LAST CHANGED IN BUILD: 188.8.131.52 *********************************************/
In this way, each individual script file can be labelled with the latest build number.
As the build version is now inserted into the comments section of the script, it allows a dba/dev a number of ways to quickly check the build an individual SQL object was created/altered in. For example:
- Viewing the script that was last deployed. See later blog on how to manage SQL script and using source control.
- If automatic schema tracking is being used then the last log entry for an object in the dbo.EventLog table will contain the ‘build version comment’ in the T-SQL statement.
- Viewing the ‘create script’ in tools such as MS SQL Management Studio.
- Viewing definitions in internal system views. For example:
SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES
This method can also be useful for checking the SQL objects changed in a previous build. For example, if the last build was ‘184.108.40.206’, then all the sprocs/functions that were created/altered in that build will have the text – ‘220.127.116.11’ – in their definitions. Therefore the following query will find them:
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%18.104.22.168%'
*Worth noting the last two methods won’t work for base tables.
The process outlined above can be used for any SQL dbms – as it only involves token replacement in script files.
The following two sections are specific to MS SQL Server showing how extended properties can be used.
Extended properties allow metadata to be added to SQL objects as name/value pairs.
Data that is added can be viewed in Management Studio – as shown in the image below:
It can also be viewed by running queries on the system view sys.extended_properties similar to those below:
--SQL object that are not columns or child objects SELECT s.name as 'Schema',o.name as 'ObjectName', p.name, p.value FROM sys.extended_properties as p INNER JOIN sys.objects as o on p.major_id=o.object_id INNER JOIN sys.schemas as s on o.schema_id=s.schema_id WHERE p.minor_id = 0 -- columns of an object <> 0 ORDER BY s.name, o.name, p.name --SQL columns SELECT s.name as 'Schema',o.name as 'ObjectName', c.name as 'ColumnName', p.name, p.value FROM sys.schemas as s INNER JOIN sys.objects as o ON o.schema_id=s.schema_id INNER JOIN sys.columns as c ON c.object_id = o.object_id INNER JOIN sys.extended_properties as p on p.minor_id = c.column_id AND p.major_id=c.object_id ORDER BY s.name, o.name, c.name, p.name
MS also provides a function – fn_listextendedproperty – that does a similar thing.
Using extended properties
MS provides 2 sprocs
- sys.sp_addextendedproperty for inserting new values.
- sys.sp_updateextendedproperty for updating existing values.
The example below shows how to carry out an insert using sys.sp_addextendedproperty:
EXEC sys.sp_addextendedproperty @name = N'MS_DescriptionExample', @value = N'Minimum inventory quantity.', @level0type = N'SCHEMA', @level0name = Production, @level1type = N'TABLE', @level1name = Product, @level2type = N'COLUMN', @level2name = SafetyStockLevel;
For versioning builds, working with these sprocs can be difficult:
- A dba/dev needs to know whether to use an insert or an update. Choose incorrectly and an error will occur. This makes it difficult to add a similar ‘script snippet’ to both create/alter scripts.
- The API is quite verbose – having to add the SQL object type eg @level1type = N’TABLE’.
- The sproc has to be executed for each individual name/value pair being entered.
To make it easier to work with a ‘wrapper sproc’ – 008_CreateProc_p_ExtendedProperty_UpSert.sql – is provided. See Source Code section below.
The following example shows how this sproc can be used:
EXEC dbo.p_ExtendedProperty_UpSert @schemaName='dbo' ,@objectNameLevel01='ObjectName' ,@lastUpdatedBy='mbaylon' -- developers name ,@desc='put any notes in here' ,@buildVersion='@BUILD_VERSION_NUMBER@'
For a ‘child object’ – a ‘@level2’ in sys.sp_addextendedproperty terms – such as a column, the following parameter would need to be added:
This sproc does a number of things:
- It checks if a property already exists and therefore can be used for both creates and alters scripts.
- Creates four predefined properties – ‘Description’, ‘LastUpdatedBuildVersion’, ‘LastUpdatedBy’, ‘LastUpdatedDate’.
- On each build the token @BUILD_VERSION_NUMBER@ is automatically replaced with an autogenerated build number on each build. In this way, the extended property for a changed SQL object is updated on each build.
The following image shows the extended properties for the dbo.BuildVersion table:
A ‘script snippet’, that executes this sproc, is included at the end of all create/alter scripts and in this way the extended properties for each SQL object are kept up to date.
The default SQL objects provided in the latest version of the database testing framework have many examples of how to use this.
- Each individual SQL object is labelled with a build version allowing it to be correlated with the overall application build/deployment.
- It is relatively easy to use with relatively little overhead – just include in a script templates.
- As outlined above, there are a number of different ways in which the build version can quickly checked.
- The sproc has 4 predefined attributes. These might not suit everyone’s needs. Though it would be relatively straight forward to change the sproc.
- The sproc provided only covers the following level01 SQL objects – table, view, procedure, function, synonym and level02 SQL objects – constraint, column, parameter, trigger.
The current version of the database testing framework does not currently include the Nant build files that are used for token replacement. If anyone would like to start using this then feel free to contact me and I will look at providing these files.
References and links
Michael Coles’ – Easy extended properties – provides a good overview and includes a really good generic sproc for working with extended properties.
This has shown how individual SQL objects can be included in the overall ‘build versioning’ of a database schema.
The next blog in this series will look at how database unit testing can be used as the ultimate check in ensuring that a database schema is exactly as required.
What do you think?
How do you include ‘build versioning’ for SQL objects?