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
MySQL Update query
I'm running my site, www.fanent.com, with Linux Web Host -- www.linuxwebhost.com. I'm pretty happy with their services, although my site seems to load slowly sometimes. I'd recommend them to anyone wanting a provider that's very friendly with PHP and MySQL...
|
|
|
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.
About the Author:
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
Read this newsletter at: http://www.sqlpronews.com/2003/0917.html |
|
|
By Sergey S.
View
Post
Click
To Comment