最近的项目，也许是产品吧，用的客户好几个，数据库也经常更新，

/*１。比较数据库表不一样的 select distinct a.id,d.name as TableName--,a.name as ColumeName from [onwerbuild_wjl2].dbo.syscolumns a,[onwerbuild_wjl2].dbo.sysobjects d where d.xtype='U' and d.name not in (select c.name from [onwerbuild_wjl_m

/*１。比较数据库表不一样的
select distinct a.id,d.name as TableName--,a.name as ColumeName
from [onwerbuild_wjl2].dbo.syscolumns a,[onwerbuild_wjl2].dbo.sysobjects d
where d.xtype='U' and
d.name not in
(select c.name from [onwerbuild_wjl_m].dbo.syscolumns b,[onwerbuild_wjl_m].dbo.sysobjects c where c.xtype='U' and b.id=c.id)
and a.id=d.id order by d.name

*/
--2.比较数据库字段
--查询库A比库B多了多少字段
--
USE onwerbuild--库A

GO
--删除所有数据
--建表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Test_del]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Test_del]
GO

CREATE TABLE [dbo].[Test_del] (
[TableName] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[ColumeName] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

DELETE FROM dbo.Test_del

DECLARE @table_name varchar(50)
DECLARE abc CURSOR FOR

--先取出一个表
select d.name as TableName
from [onwerbuild_AS].dbo.sysobjects d --库A
where d.xtype='U' order by d.name

OPEN abc

FETCH NEXT FROM abc
into @table_name

WHILE (@@FETCH_STATUS = 0)
begin
--一个表一个表的对应
--把不同的字段插入
USE onwerbuild_AS--库A
Insert into [onwerbuild].dbo.Test_del
select d.name as TableName,a.name as ColumeName
from [onwerbuild_AS].dbo.syscolumns a,[onwerbuild_AS].dbo.sysobjects d --库A
where d.name=@table_name
and a.name not in
(select b.name from [onwerbuild].dbo.syscolumns b,[onwerbuild].dbo.sysobjects c where c.name=@table_name and b.id=c.id)--库B
and a.id=d.id order by d.name
FETCH NEXT FROM abc
into @table_name

set nocount on
end
CLOSE abc
DEALLOCATE abc

select * from [onwerbuild].dbo.Test_del
select distinct TableName from [onwerbuild].dbo.Test_del
GO

声明：本网页内容旨在传播知识，若有侵权等问题请及时与本网联系，我们将在第一时间删除处理。TEL:177 7030 7066 E-MAIL:11247931@qq.com