不同库表间(含字段)比较的SQL代码

不同库表间(含字段)比较的SQL代码

use accdb1 --为zy旧库,Accdb为液晶版标准库,已知Accdb库比Accdb1库多出表和字段,求多出的表和字段

SELECT accdb.table_name AS accdb_table_name,

accdb.columns_name AS accdb_columns_name,

accdb1.table_name AS accdb1_table_name,

accdb1.columns_name AS accdb1_columns_name

FROM (SELECT Accdb.dbo.sysobjects.name AS table_name,

Accdb.dbo.syscolumns.name AS columns_name

FROM Accdb.dbo.syscolumns INNER JOIN

Accdb.dbo.sysobjects ON

Accdb.dbo.syscolumns.id = Accdb.dbo.sysobjects.id

WHERE (NOT (Accdb.dbo.syscolumns.name LIKE '@%')) AND

(Accdb.dbo.sysobjects.xtype = 'U')) accdb LEFT OUTER JOIN

(SELECT sysobjects.name AS table_name,

syscolumns.name AS columns_name

FROM syscolumns INNER JOIN

sysobjects ON syscolumns.id = sysobjects.id

WHERE (NOT (syscolumns.name LIKE '@%')) AND (sysobjects.xtype = 'U'))

accdb1 ON accdb.columns_name = accdb1.columns_name AND

accdb.table_name = accdb1.table_name

WHERE (accdb1.table_name IS NULL) OR

(accdb1.columns_name IS NULL) order by accdb.table_name,accdb.columns_name