SQL 脚本重复执行约束

可以多次执行SP 的脚本

1. 在脚本头写上这个，表示如果存储过程不存在，先创建一个，否则，直接修改。

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'my_query_sp')
BEGIN
EXEC('CREATE PROCEDURE [dbo].[my_query_sp] AS RETURN')
END
 
ALTER PROCEDURE [dbo].[my_query_sp]

另外一种写法：

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS
(
select * from sys.procedures
inner join sys.schemas on procedures.schema_id = schemas.schema_id
where
schemas.name = 'dbo' and
procedures.name = 'tbl_sp'
)
Begin
EXEC sp_ExecuteSQL N'CREATE PROC dbo.tbl_sp as RAISERROR(''dbo.tbl_spis incomplete'', 16, 127);';

PRINT '[Success] Stored Procedure dbo.tbl_sp is created.';
End

ALTER PROCEDURE [dbo].tbl_sp
...
As
BEGIN
select ***
END

PRINT '[Success] Stored Procedure dbo.tbl_sp is modified.';

----------------------------------------------------------

2. Check if column in the table.

IF NOT EXISTS (
SELECT * FROM sys.columns 
WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]') 
AND name = 'MyColumn'
)
BEGIN
print('MyColumn Not Exist, Add Column')
ALTER TABLE [dbo].[MyTable]
ADD MyColumn [bit] NOT NULL DEFAULT 1
print('Added')
END
ELSE
BEGIN
print('MyColumn Exist, no need to Add')
END

3. 约束表中内容不重复

ALTER TABLE tbl ADD CONSTRAINT PK_tbl PRIMARY KEY CLUSTERED ( column1 ASC, column2 ASC );

4. if row not exist, insert

IF NOT EXISTS( select * from dbo.table where idrow= '1')
BEGIN
PRINT 'INSERT!!';
INSERT INTO table VALUES('description',0,0);
END

DECLARE @rowCount integer
SELECT @rowCount = COUNT(1) FROM [dbo].[table]
IF @rowCount = 0 BEGIN

BEGIN

　　一堆Insert

END

5. 如果Constraint不为空，删掉并重新添加。

IF OBJECT_ID('[DF_tbl_ChangedBy]', 'C') IS NOT NULL 
begin
    ALTER TABLE dbo.[tbl] DROP CONSTRAINT [DF_tbl_ChangedByEID]
    print 'abc';
    ALTER TABLE dbo.[tbl]
    ADD CONSTRAINT [DF_tbl_ChangedBy] DEFAULT (N'') for ChangedBy
end

SQL 脚本 重复执行 约束

SQL 脚本重复执行约束