使用SQL Delta比较两个数据库结构,并生成SQL脚本。
点击菜单列表————》New,创建比较项目。
本例中:db_Mail 数据库为最新。db_Mail2数据库版本为 旧版本。
填写完成后,点击Compare Databases进行比较。
界面如下:
已知db_Mail 数据库中有两张表,MailInfo,UserInfo 。而db_Mail2数据库中无表。
然后点击————》Sync则进入如下界面。
对比后,点击 Save 则创建缺省的表的SQL语句。Run则将db_Mail2的数据结构统一为db_Mail。
BEGIN TRANSACTION
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
PRINT 'Creating dbo.MailInfo Table'
GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
CREATE TABLE [dbo].[MailInfo] (
[id] [int] IDENTITY (1, 1) NOT NULL,
[uname] [nvarchar] (500) COLLATE Chinese_PRC_CI_AS NULL,
[usex] [int] NULL,
[qq] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
[email] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
[address] [nvarchar] (500) COLLATE Chinese_PRC_CI_AS NULL,
[UserID] [int] NULL CONSTRAINT [UserID] DEFAULT (0.00)
)
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
ALTER TABLE [dbo].[MailInfo] ADD CONSTRAINT [PK_MailInfo] PRIMARY KEY CLUSTERED ([id])
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
BEGIN
PRINT 'dbo.MailInfo Table Added Successfully'
COMMIT TRANSACTION
END ELSE
BEGIN
PRINT 'Failed To Add dbo.MailInfo Table'
END
GO
BEGIN TRANSACTION
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
PRINT 'Creating dbo.UserInfo Table'
GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
CREATE TABLE [dbo].[UserInfo] (
[rid] [int] IDENTITY (1, 1) NOT NULL,
[uname] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
[usex] [int] NULL,
[email] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
)
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
BEGIN
PRINT 'dbo.UserInfo Table Added Successfully'
COMMIT TRANSACTION
END ELSE
BEGIN
PRINT 'Failed To Add dbo.UserInfo Table'
END
GO