Many firms, developing client-server applications, have a lot of the databases, from developers DB to testers DB. Keeping DB in actual state can be automated easily only if there is centralized access to all of them and there are no developments on any of them. The last influences a lot of the DB structure because there is a possibility to break it while developing.
Because of this there appears a problem of comparing DB structures which are under development, testing and being used by the end-users.
There are number of traditional ways to solve the above problem of finding differences between the etalon DB and the DB being checked. The most obvious way is getting metainfo from the both DBs in the client application and following comparing them. Along with ease and universality, this method is rather time-taking to implement on the client side. Meanwhile, to find any problems with structure integrity a short analysis without any UI is usually enough.
This problem can be solved easily in MSSQL server by using server stored procedures and a possibility of this server to make heterogeneous queries. The metadata structure of this server is open and can be studied via SQL Server Books Online supplied with it and Microsoft’s web-site – MSDN.
The most of server’s objects are situated in one system table, sysobjects. The heart of the method is to make a heterogeneous query which joins the sysobjects tables of checked and etalon DBs and to look through the resulting cursor to compare parameters of the same objects and then, to produce the results for user. For instance, we can write the following statement batch:
select * from etalondb.dbo.sysobjects etalon left join sysobjects test on test.[name] = etalon.[name] where etalon.[xtype] = 'U'
Running this script for the etalon DB and then for the DB being checked, we can get a full picture of differences between the two DBs (absent and unnecessary tables). But, it is rather inconvenient to analyze the result of comparing as is. A more suitable form is messages saying about presence, absence or inconsistency of the checked objects. To do that, let us improve our algorithm by using temporary cursors. At that we use the PRINT statement to show the results.
declare @o_name varchar(50), @o_id int, @o_type char(2)
declare @c_name varchar(50), @c_id int
declare o_sysobjects cursor fast_forward for
select [name], [id], [type] from etalondb..sysobjects
where (type in ('F', 'U', 'V')) order by [name]
open o_sysobjects
fetch o_sysobjects into @o_name, @o_id, @o_type
while @@fetch_status = 0
begin
select @c_name = null, @c_id = null
select @c_name = [name], @c_id = [id] from sysobjects
where [name] = @o_name and isnull(objectproperty([id], 'ISMSShipped'), 1) 1
if (@c_name is not null)
begin
if (@o_type = 'U')
begin
exec sp_check_columns @c_name
exec sp_check_indexes @c_name
end else
if (@o_type = 'V')
begin
exec sp_check_columns @c_name
end else
if (@o_type = 'F')
begin
exec sp_check_foreignkey @c_name
end
select 'OK! ' + @o_type + '-' + @c_name
end else
if (isnull(objectproperty(@c_id, 'ISMSShipped'), 1) 1)
begin
select 'ERROR! etalondb.' + @o_type + '-' + @o_name
end
fetch o_sysobjects into @o_name, @o_id, @o_type
end
close o_sysobjects
deallocate o_sysobjects
Because of presence of the system objects in the same table we have to make the following check-up – (isnull(objectproperty(@c_id, ‘ISMSShipped’), 1) 1). Such objects include rules, defines, indexes which have been built with statistics by MSSQL server to perform self-tuning. These objects do not have to be serviced by developers and their presence or absence shouldn’t affect system work ability as a whole.
The need of join objects by the names in different DBs is because of ambiguity of their numerical identifiers, which are unique only within DB. That is why we should note that it is impossible to join by the names objects which have been made by the server itself. It happens because the server automatically assigns names to those objects. Fortunately, developers should just know about presence of an object and its properties, as a rule.
Checking columns may consist of simple check for presence as well as comparing data types which these columns store.
create procedure sp_check_columns @table_name varchar(50) as
begin
declare @o_name varchar(50), @o_table_id int, @o_type_name varchar(50),
@o_length int, @o_isnullable int, @o_status int
declare @c_name varchar(50), @c_table_id int, @c_type_name varchar(50),
@c_length int, @c_isnullable int, @c_status int
set @o_table_id = object_id('etalondb..' + @table_name)
set @c_table_id = object_id(@table_name)
declare o_syscolumns cursor fast_forward for
select c.[name], t.[name], c.length , c.isnullable, c.status
from etalondb..syscolumns c, etalondb..systypes t
where c.usertype = t.usertype and c.[id] = @o_table_id
open o_syscolumns
fetch o_syscolumns into @o_name, @o_type_name, @o_length, @o_isnullable, @o_status
while @@fetch_status = 0
begin
select @c_name = null
select @c_name = c.[name], @c_type_name = t.[name],
@c_length = c.length, @c_isnullable = c.isnullable, @c_status = c.status
from syscolumns c, systypes t
where (c.usertype = t.usertype) and (c.[id] = @c_table_id) and (c.[name] = @o_name)
if (@c_name is null) or (@o_type_name @c_type_name)
or (@o_length @c_length)
or (@o_isnullable @c_isnullable) or (@o_status @c_status)
begin
select 'ERROR! etalondb.' + @table_name
end
fetch o_syscolumns into @o_name, @o_type_name, @o_length,
@o_isnullable, @o_status
end
close o_syscolumns
deallocate o_syscolumns
end
In all cases the results are written to the console only if an error has been found. It spares the developer from conservative review and analysis of the log.
The hardest task is checking indexes. It depends on complexity of store metadata structure in MSSQL server. One of such ways to implement this checking is in a full script listing for comparing metadata of the two DBs, which can be downloaded here.
We should note more features of checking objects which are based on scripts. They are server stored procedures, triggers, views, as well as rules and default constraints. The difficult is that it is very often impossible to make sure that the scripts are identical by using symbol-by-symbol comparing because the server adds some comments to the script headers. It may happen while getting script of the existing database by using Enterprise Manager or SQL-DMO Objects. Fortunately, manually added scripts aren’t affected by the server so that we can use simple check-ups.
create procedure sp_check_object_script @object_name varchar(50) as
begin
declare @o_len int, @c_len int
select @o_len = len([ctext]) from etalondb..syscomments
where [id] = object_id('etalondb..' + @object_name)
select @c_len = len([ctext]) from syscomments where [id] = object_id(@object_name)
if (@o_len is null) or (@c_len is null)
begin
select 'ERROR! in calling of sp_check_object_script for object: ' + @object_name
return
end
if (@o_len @c_len)
begin
select 'ERROR! the definitions of ' + @object_name + ' are differ'
end
end
In summary, we would like to note that performance of these check-ups depends on the DB structure evolution and performance and server utilized capacity. It is because, as a rule, there are heterogeneous queries in our scripts which run slower then ordinary queries within one DB.
The full version of the scripts used in this article can be downloaded here.
Sergey S. is a member of the Clever Components Team. The history of our site began from an Interbase DataPump and the original idea was to provide Delphi / Borland C++ and Interbase developers with high quality consultations. Nowadays we have many satisfied customers and can offer some excellent tools and utilities. We wish to maintain the highest standards in our service and keep our customers happy. Please feel free to contact us at info@clevercomponents.com
