A quick solution is to calculate a checksum of the database objects and its columns.
To hold the version number of the database and its checksum a (singleton) table is created.
CREATE TABLE [dbo].[Database_Version]
(
[Database_Version_No] nvarchar(128) NOT NULL,
[Database_Version_Checksum] bigint NOT NULL,
CONSTRAINT PK_Database_Version PRIMARY KEY ([Database_Version_No])
);
The table can be initialized by a INSERT statement:INSERT INTO [dbo].[Database_Version] ([Database_Version_No], [Database_Version_Checksum])
VALUES ('0', 42);
The table is used by two stored procedures - one to read:
CREATE PROCEDURE [dbo].[Get_Database_Version]
AS
BEGIN
SET NOCOUNT ON;
-- Check stored checksum
-- Calculate checksum
DECLARE @objects_checksum bigint;
SELECT @objects_checksum = SUM(CAST(CHECKSUM(OBJECT_DEFINITION([object_id])) AS bigint))
FROM sys.objects
WHERE is_ms_shipped = 0;
DECLARE @columns_checksum bigint;
SELECT @columns_checksum = SUM(CAST(CHECKSUM(OBJECT_DEFINITION([column_id])) AS bigint))
FROM sys.columns;
-- Check is the checksum stored is equal to the calculated checksum
IF (@objects_checksum + @columns_checksum) <> (
SELECT [Database_Version_Checksum]
FROM [dbo].[Database_Version]
)
BEGIN
DECLARE @calculated_checksum nvarchar(128) = CAST((@objects_checksum + @columns_checksum) AS nvarchar(128));
DECLARE @stored_checksum nvarchar(128) = CAST((SELECT [Database_Version_Checksum] FROM [dbo].[Database_Version]) AS nvarchar(128));
RAISERROR(N'The calculated checksum (%s) is not equal to the stored checksum (%s).',10, 1,@calculated_checksum,@stored_checksum) WITH NOWAIT;
END
-- Get stored checksum
SELECT [Database_Version_No], [Database_Version_Checksum]
FROM [dbo].[Database_Version];
END
...and one to update:CREATE PROCEDURE [dbo].[Set_Database_Version]
@Database_Version_No nvarchar(128)
AS
BEGIN
SET NOCOUNT ON;
-- Check if the given Database Version value is different from the existing
IF @Database_Version_No = (
SELECT [Database_Version_No]
FROM [dbo].[Database_Version]
)
RAISERROR('You have to update the Database Version to another value than the existing one.', 18, 0) WITH NOWAIT;
-- Calculate checksum
DECLARE @objects_checksum bigint;
SELECT @objects_checksum = SUM(CAST(CHECKSUM(OBJECT_DEFINITION([object_id])) AS bigint))
FROM sys.objects
WHERE is_ms_shipped = 0;
DECLARE @columns_checksum bigint;
SELECT @columns_checksum = SUM(CAST(CHECKSUM(OBJECT_DEFINITION([column_id])) AS bigint))
FROM sys.columns;
-- Check is the checksum stored is equal to the calculated checksum
IF (@objects_checksum + @columns_checksum) = (
SELECT [Database_Version_Checksum]
FROM [dbo].[Database_Version]
)
RAISERROR('The calculated checksum is equal to the stored checksum.',18, 1) WITH LOG;
-- @objects_checksum + @columns_checksum
UPDATE [dbo].[Database_Version]
SET [dbo].[Database_Version].[Database_Version_No] = @Database_Version_No,
[dbo].[Database_Version].Database_Version_Checksum = @objects_checksum + @columns_checksum;
END
The get procedure also checks if the stored checksum is like to actual calculated checksum. Of course the checksum inserted above is not correct, so the get procedure raise an error:
EXEC [dbo].[Get_Database_Version];
{Message} The calculated checksum (3381089942582) is not equal to the stored checksum (42).
The update procedure calculate a checksum and update the stored checksum. The version number is actually just a string, so you can make your own version standard.
EXEC [dbo].[Set_Database_Version] @Database_Version_No = '3.1';
The get procedure now executes with success.
Database_Version_No Database_Version_Checksum
------------------- -------------------------
3.1 3381089942582
3.1 3381089942582
If a object is changes, e.g. a column is added
ALTER TABLE [dbo].[Database_Version] ADD
[Database_Version_Description] nvarchar(MAX) NULL;
Now the get procedure raises a error
The calculated checksum (3383237426229) is not equal to the stored checksum (3381089942582).
If the column is dropped,ALTER TABLE [dbo].[Database_Version]
DROP COLUMN [Database_Version_Description];
the get procedure executes with success.
Discussion
The database version objects are in the schema "dbo", but you might want to keep this schema clean.
When the get procedure fails, you have no indication on what is different. You could use a comparison tool like Red Gate SQL Compare.
There is no timestamp in the solution, but you could format the version as a timestamp, e.g. "2009-09-17A".
The version checksum calculation is somewhat simple. It could also include indexes, users, files and what else you need.
A version and checksum could be calculated and stored for each database. Some changes to the version objects are needed, but it should not be a problem to the dedicated DBA.
Posts
