VB and VBA Users Source Code: Comparing the columns of two tables on different databases
[
Home
|
Contents
|
Search
|
Reply
| Previous | Next ]
VB/VBA Source Code
Comparing the columns of two tables on different databases
By:
Andrew Baker
Email (spam proof):
Email the originator of this post
Date:
Wednesday, July 17, 2002
Hits:
1015
Category:
Database/SQL/ADO
Article:
The following example demonstrates how to compare the columns in two tables residing in different databases. Note, this was written in Sybase 11.5 which doesn't support dynamic SQL. I will rewrite this once I get round to upgrading: --Before running this, change database1 and database2 to the names of the --two databases you want to compare and the variable @table_name to the name of the table. declare @table_name varchar(255) declare @table_id1 int declare @table_id2 int set nocount on select @table_name = "MyTable" --Get object ID's of tables select @table_id1 = id from database1.dbo.sysobjects where name = @table_name select @table_id2 = id from database2.dbo.sysobjects where name = @table_name --Populate the temp table with the details on the columns from both the tables select "database1" as database_name, name as column_name, type as column_type, length as column_length into #tmp_names1 from database1.dbo.syscolumns where id = @table_id1 order by column_name select "database2" as database_name, name as column_name, type as column_type, length as column_length into #tmp_names2 from database2.dbo.syscolumns where id = @table_id2 order by column_name --Return details on the columns which are different select * from #tmp_names1 where not exists ( select 1 from #tmp_names2 where #tmp_names1.column_name = #tmp_names2.column_name and #tmp_names1.column_type = #tmp_names2.column_type and #tmp_names1.column_length = #tmp_names2.column_length ) union select * from #tmp_names2 where not exists ( select 1 from #tmp_names1 where #tmp_names1.column_name = #tmp_names2.column_name and #tmp_names1.column_type = #tmp_names2.column_type and #tmp_names1.column_length = #tmp_names2.column_length ) order by column_name --Drop the temporary table drop table #tmp_names1 drop table #tmp_names2 set nocount off
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
