使用sql compare生成的sql语句

创建表以及主键

判断表是否存在

OBJECT_ID

判断主键是否存在

SELECT 1 FROM sys.indexes WHERE name = N'PK_LISA_NoUseWebpartReplacement' AND object_id = OBJECT_ID(N'[dbo].[LISA_NoUseWebpartReplacement]')

 

/*
Run this script on a database with the schema represented by:

        WASYGSHA01-1020\SQL2014.Test    -  This database will be modified. The scripts folder will not be modified.

to synchronize it with a database with the schema represented by:

        WASYGSHA01-1020\SQL2014.Test

You are recommended to back up your database before running this script

Script created by SQL Compare version 13.4.5.6953 from Red Gate Software Ltd at 11/13/2018 5:18:41 PM

*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL Serializable
GO
BEGIN TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[LISA_NoUseWebpartReplacement]'
GO
IF OBJECT_ID(N'[dbo].[LISA_NoUseWebpartReplacement]', 'U') IS NULL
CREATE TABLE [dbo].[LISA_NoUseWebpartReplacement]
(
[Id] [int] NOT NULL,
[Category] [nvarchar] (255) NULL,
[WebpartCode] [nvarchar] (255) NOT NULL,
[WebpartName] [nvarchar] (255) NULL,
[WebpartFile] [nvarchar] (255) NULL,
[Replacement] [nvarchar] (255) NULL
)
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating primary key [PK_LISA_NoUseWebpartReplacement] on [dbo].[LISA_NoUseWebpartReplacement]'
GO
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'PK_LISA_NoUseWebpartReplacement' AND object_id = OBJECT_ID(N'[dbo].[LISA_NoUseWebpartReplacement]'))
ALTER TABLE [dbo].[LISA_NoUseWebpartReplacement] ADD CONSTRAINT [PK_LISA_NoUseWebpartReplacement] PRIMARY KEY CLUSTERED  ([WebpartCode])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
COMMIT TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
-- This statement writes to the SQL Server Log so SQL Monitor can show this deployment.
IF HAS_PERMS_BY_NAME(N'sys.xp_logevent', N'OBJECT', N'EXECUTE') = 1
BEGIN
    DECLARE @databaseName AS nvarchar(2048), @eventMessage AS nvarchar(2048)
    SET @databaseName = REPLACE(REPLACE(DB_NAME(), N'\', N'\\'), N'"', N'\"')
    SET @eventMessage = N'Redgate SQL Compare: { "deployment": { "description": "Redgate SQL Compare deployed to ' + @databaseName + N'", "database": "' + @databaseName + N'" }}'
    EXECUTE sys.xp_logevent 55000, @eventMessage
END
GO
DECLARE @Success AS BIT
SET @Success = 1
SET NOEXEC OFF
IF (@Success = 1) PRINT 'The database update succeeded'
ELSE BEGIN
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
    PRINT 'The database update failed'
END
GO

 

创建存储过程

先判断存储过程是否存在，如果存在就先drop，然后创建。如果不存在就直接创建

/*
Run this script on a database with the schema represented by:

        WASYGSHA01-1020\SQL2014.Test    -  This database will be modified. The scripts folder will not be modified.

to synchronize it with a database with the schema represented by:

        WASYGSHA01-1020\SQL2014.Test

You are recommended to back up your database before running this script

Script created by SQL Compare version 13.6.3.8160 from Red Gate Software Ltd at 11/14/2018 11:11:52 AM

*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL Serializable
GO
BEGIN TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping [dbo].[pi_NoUseWebpartReplacement]'
GO
IF OBJECT_ID(N'[dbo].[pi_NoUseWebpartReplacement]', 'P') IS NOT NULL
DROP PROCEDURE [dbo].[pi_NoUseWebpartReplacement]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[pi_NoUseWebpartReplacement]'
GO
IF OBJECT_ID(N'[dbo].[pi_NoUseWebpartReplacement]', 'P') IS NULL
EXEC sp_executesql N'-- =============================================
-- Author:        Chuck Lu
-- Create date: 2018-11-14
-- Description:    
-- =============================================
CREATE PROCEDURE [dbo].[pi_NoUseWebpartReplacement]
    -- Add the parameters for the stored procedure here
    @Id INT ,
    @Category NVARCHAR(255) ,
    @WebpartCode NVARCHAR(255) ,
    @WebpartName NVARCHAR(255) ,
    @WebpartFile NVARCHAR(255) ,
    @Replacement NVARCHAR(255)
AS
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
        IF EXISTS (   SELECT 1
                      FROM   dbo.LISA_NoUseWebpartReplacement
                      WHERE  WebpartCode = @WebpartCode )
            BEGIN
                UPDATE dbo.LISA_NoUseWebpartReplacement
                SET    Category = @Category ,
                       WebpartName = @WebpartName ,
                       WebpartFile = @WebpartFile ,
                       Replacement = @Replacement
                WHERE  WebpartCode = @WebpartCode;
            END;
        ELSE
            BEGIN
                INSERT INTO dbo.LISA_NoUseWebpartReplacement ( Id ,
                                                               Category ,
                                                               WebpartCode ,
                                                               WebpartName ,
                                                               WebpartFile ,
                                                               Replacement )
                VALUES ( @Id ,          -- Id - int
                         @Category ,    -- Category - nvarchar(255)
                         @WebpartCode , -- WebpartCode - nvarchar(255)
                         @WebpartName , -- WebpartName - nvarchar(255)
                         @WebpartFile , -- WebpartFile - nvarchar(255)
                         @Replacement   -- Replacement - nvarchar(255)
                    );
            END;
    END;
'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
COMMIT TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
-- This statement writes to the SQL Server Log so SQL Monitor can show this deployment.
IF HAS_PERMS_BY_NAME(N'sys.xp_logevent', N'OBJECT', N'EXECUTE') = 1
BEGIN
    DECLARE @databaseName AS nvarchar(2048), @eventMessage AS nvarchar(2048)
    SET @databaseName = REPLACE(REPLACE(DB_NAME(), N'\', N'\\'), N'"', N'\"')
    SET @eventMessage = N'Redgate SQL Compare: { "deployment": { "description": "Redgate SQL Compare deployed to ' + @databaseName + N'", "database": "' + @databaseName + N'" }}'
    EXECUTE sys.xp_logevent 55000, @eventMessage
END
GO
DECLARE @Success AS BIT
SET @Success = 1
SET NOEXEC OFF
IF (@Success = 1) PRINT 'The database update succeeded'
ELSE BEGIN
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
    PRINT 'The database update failed'
END
GO

 

作者:Chuck Lu    GitHub