SQL Server 项目中 SQL 脚本更新、升级方式，防止多次重复执行

MySQL 项目中 SQL 脚本更新、升级方式，防止多次重复执行

Oracle 项目中 SQL 脚本更新方式

一套代码，多家部署时，在SQL脚本升级时，通过一个SQL文件给运维，避免出现SQL执行序顺出错及漏执行SQL

常用的如下：

--如果不存在这条数据，就添加
IF NOT EXISTS (SELECT 1 FROM dbo.Settings WHERE PropertyName = 'DBVersion')
    INSERT INTO dbo.Settings(ID, Code, Value)  VALUES(NEWID(),'DBVersion','2018')
GO

--可以通过版本号控制
IF EXISTS (SELECT 1 FROM dbo.Settings WHERE PropertyName='DBVersion' AND CONVERT(INT, CONVERT(VARCHAR(10), PropertyValue)) <= 2019)
BEGIN
    UPDATE TABLE1 SET Field1 = 'abc' WHERE field2 = 0 
    UPDATE TABLE2 SET FIELD2 = '1900-01-01 00:00:00' WHERE [Status] = 1
    UPDATE TABLE3 SET FIELD3 = '1900-01-01 00:00:00' WHERE [Status] = 1
    
    UPDATE dbo.Settings SET PropertyValue = '2020' WHERE PropertyName='DBVersion'
END
GO  

--如果表里不存在字段，就添加
IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TABLE4' AND COLUMN_NAME = 'COLUMN2' AND TABLE_SCHEMA = 'dbo')
BEGIN
    ALTER TABLE TABLE4 Add  COLUMN2 nvarchar(31)
END 
GO 

--存储过程先删除，再创建
IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'P' AND name = 'PRO_TEST')
DROP PROCEDURE PRO_TEST
GO
CREATE PROCEDURE [dbo].[PRO_TEST]
    @StartDate    DATETIME = NULL,
    @EndDate    DATETIME = NULL,
    @Num    INT = NULL
AS
SELECT * FROM LogTable
WHERE Num = ISNULL(@Num, Num)
    AND CreatedDate >= ISNULL(@StartDate, CreatedDate)
    AND CreatedDate <= ISNULL(@EndDate, CreatedDate) + '23:59:59'
ORDER BY CreatedDate DESC
GO

 如果表不存在，创建表

IF NOT EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'Table_name')
BEGIN 

    CREATE TABLE Table_name(
        Id varchar(36),
        HospitalId varchar(36),
        HospitalName nvarchar(80),
        DeptId varchar(36),
        DeptName nvarchar(80)
    );
 
    EXECUTE sp_addextendedproperty N'MS_Description', 'ID', N'user', N'dbo', N'table', N'Sys_DeptProcessRelation', N'column', N'Id';
    EXECUTE sp_addextendedproperty N'MS_Description', '医院ID', N'user', N'dbo', N'table', N'Sys_DeptProcessRelation', N'column', N'HospitalId';
    EXECUTE sp_addextendedproperty N'MS_Description', '医院名称', N'user', N'dbo', N'table', N'Sys_DeptProcessRelation', N'column', N'HospitalName';
    EXECUTE sp_addextendedproperty N'MS_Description', '科室ID', N'user', N'dbo', N'table', N'Sys_DeptProcessRelation', N'column', N'DeptId';
    EXECUTE sp_addextendedproperty N'MS_Description', '科室名称', N'user', N'dbo', N'table', N'Sys_DeptProcessRelation', N'column', N'DeptName';

    PRINT 'Create table Table_name Successfully!';

END
GO