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

GO

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

GO

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

GO

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

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