Creating tables and primary keys
Determining whether a table exists
OBJECT_ID

Determine if the primary key exists

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

Create stored procedure

First determine whether the stored procedure exists, if it exists, drop it first, and then create it. If it doesn’t exist, create it directly

/*
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