Quantcast

Comparing database structures in MSSQL server

Get the WebProNews Newsletter:
[ Business]

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

Comparing database structures in MSSQL server
Comments Off
About Sergey S.
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 WebProNews Writer
Top Rated White Papers and Resources

Comments are closed.