创建表以及主键
判断表是否存在
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.TestYou are recommended to back up your database before running this scriptScript 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 OFFGOSET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ONGOSET XACT_ABORT ONGOSET TRANSACTION ISOLATION LEVEL SerializableGOBEGIN TRANSACTIONGOIF @@ERROR <> 0 SET NOEXEC ONGOPRINT N'Creating [dbo].[LISA_NoUseWebpartReplacement]'GOIF OBJECT_ID(N'[dbo].[LISA_NoUseWebpartReplacement]', 'U') IS NULLCREATE 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)GOIF @@ERROR <> 0 SET NOEXEC ONGOPRINT N'Creating primary key [PK_LISA_NoUseWebpartReplacement] on [dbo].[LISA_NoUseWebpartReplacement]'GOIF 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])GOIF @@ERROR <> 0 SET NOEXEC ONGOCOMMIT TRANSACTIONGOIF @@ERROR <> 0 SET NOEXEC ONGO-- 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') = 1BEGIN 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, @eventMessageENDGODECLARE @Success AS BITSET @Success = 1SET NOEXEC OFFIF (@Success = 1) PRINT 'The database update succeeded'ELSE BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION PRINT 'The database update failed'ENDGO
创建存储过程
先判断存储过程是否存在，如果存在就先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.TestYou are recommended to back up your database before running this scriptScript 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 OFFGOSET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ONGOSET XACT_ABORT ONGOSET TRANSACTION ISOLATION LEVEL SerializableGOBEGIN TRANSACTIONGOIF @@ERROR <> 0 SET NOEXEC ONGOPRINT N'Dropping [dbo].[pi_NoUseWebpartReplacement]'GOIF OBJECT_ID(N'[dbo].[pi_NoUseWebpartReplacement]', 'P') IS NOT NULLDROP PROCEDURE [dbo].[pi_NoUseWebpartReplacement]GOIF @@ERROR <> 0 SET NOEXEC ONGOPRINT N'Creating [dbo].[pi_NoUseWebpartReplacement]'GOIF OBJECT_ID(N'[dbo].[pi_NoUseWebpartReplacement]', 'P') IS NULLEXEC 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;'GOIF @@ERROR <> 0 SET NOEXEC ONGOCOMMIT TRANSACTIONGOIF @@ERROR <> 0 SET NOEXEC ONGO-- 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') = 1BEGIN 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, @eventMessageENDGODECLARE @Success AS BITSET @Success = 1SET NOEXEC OFFIF (@Success = 1) PRINT 'The database update succeeded'ELSE BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION PRINT 'The database update failed'ENDGO