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
GOCreate 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