SQL Server創建或者刪除表、字段、索引、視圖、觸發器前判斷是否存在。

目錄

1.?SQL Server創建表之前判斷表是否存在

2.?SQL Server新增字段之前判斷是否存在

3. SQL Server刪除字段之前判斷是否存在

4. SQL Server新增索引之前判斷是否存在

5. SQL Server判斷視圖是否存在并創建

6. SQL Server判斷觸發器是否存在并創建

7. 創建SYNONYM 之前判斷是否存在

---

1.?SQL Server創建表之前判斷表是否存在

IF OBJECT_ID('[dbo].[Documents]', 'U') IS  NULL  
begin-- This table used to save recent 2 years documentsCREATE TABLE [dbo].[Documents] ([Id]                        INT             NOT NULL,[DocumentGroupId]           INT             NOT NULL,[FileName]                  VARCHAR (200)   NULL,[FilePath]                  VARCHAR (250)   NULL,[CreateDate]                DATETIME        NOT NULL,[ModifyDate]                DATETIME        NOT NULL,[DocumentStatus]            VARCHAR (2)     NULL)END
GO

2.?SQL Server新增字段之前判斷是否存在

--[dbo].[AgencyDocuments] table name
--EffectiveDateTest column name. 此處不可以用[EffectiveDateTest]
if COL_LENGTH('[dbo].[AgencyDocuments]','EffectiveDateTest') is null
beginalter table [dbo].[AgencyDocuments] add [EffectiveDateTest] DATETIME NULL
end
go

3. SQL Server刪除字段之前判斷是否存在

--[dbo].[AgencyDocuments] table name
--EffectiveDateTest column name. 此處不可以用[EffectiveDateTest]
if COL_LENGTH('[dbo].[AgencyDocuments]','EffectiveDateTest') is NOT null
beginalter table [dbo].[AgencyDocuments] drop column [EffectiveDateTest] 
end
go

4. SQL Server新增索引之前判斷是否存在

IF NOT EXISTS (SELECT top 1 1 FROM sys.indexes  WHERE name='IX_AgencyDocument_Id' AND object_id = OBJECT_ID('[dbo].[AgencyDocuments]'))
BEGIN    CREATE NONCLUSTERED INDEX IX_AgencyDocument_Id ON [dbo].[AgencyDocuments] ([Id])INCLUDE ([DocumentGroupId],[FileName],[FilePath],[CreateDate],[DocumentStatus])
END
GO

5. SQL Server判斷視圖是否存在并創建

-- View存在則刪除
IF OBJECT_ID('vw_AgencyDocuments') IS NOT NULL
begindrop view  vw_AgencyDocuments
end
GO
--創建View
create view vw_AgencyDocuments as 
select Id from [dbo].[AgencyDocuments]
go

6. SQL Server判斷觸發器是否存在并創建

-- 判斷是否存在，存在則刪除
IF OBJECT_ID ('[dbo].[TRIGGER_INSERT_AGENCYDOCUMENTS]', 'TR') IS NOT NULL 
begindrop trigger [dbo].[TRIGGER_INSERT_AGENCYDOCUMENTS]  
end
GO-- 創新新的觸發器
CREATE TRIGGER [dbo].[TRIGGER_UPDATE_AGENCYDOCUMENTS] ON [dbo].[Document] AFTER UPDATE
AS 
BEGINSET NOCOUNT ON;UPDATE mSET [ModifyDate] = n.[ModifyDate]  ,[Status] = n.[Status]      ,[DocumentStatus] = n.[DocumentStatus] FROM [dbo].[AgencyDocuments] m JOIN INSERTED n on m.Id=n.Id SET NOCOUNT OFF;
ENDGO

7. 創建SYNONYM 之前判斷是否存在

if not exists (select * from sys.synonyms where object_id=OBJECT_ID('[dbo].[AgencyDocuments]'))
beginCREATE SYNONYM [dbo].[AgencyDocuments] FOR [ABC_REPL].[dbo].[AgencyDocuments]
end
go