Comparing database structures in MSSQL server

	Comparing database structures in MSSQL server

Submitted on June 25, 2002

The full version of the scripts used in this article can be downloaded here.

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.

Clever Components Team.
Please feel free to Contact Us
We are welcome to any comments and suggestions.