sql server2012重复执行创建表视图sql及带行号的视图

1、可重复操作(创建表，视图)的sql语句判断

IF EXISTS ( SELECT *
FROM sys.foreign_keys
WHERE object_id = OBJECT_ID(N'[register].[fk_UsersInRole_Role]')
AND parent_object_id = OBJECT_ID(N'[register].[UsersInRole]') )
ALTER TABLE [register].[UsersInRole] DROP CONSTRAINT [fk_UsersInRole_Role]
GO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[register].[Role]')
AND type IN ( N'U' ) )
DROP TABLE register.[Role]
GO

 

2、CREATE VIEW [SafeWatch].[MissionInvalidItemView]

AS
SELECT ROW_NUMBER() OVER(ORDER BY aci.Id ASC) AS RowNumber,
ac.AppCheckItemId,
ac.RejectReason,
ac.PhotoLocation,
ac.MissionId,
aci.[Text] AS AppCheckItemText,
aci.Title AS AppCheckItemTitle,
mii.FileTypeKey
FROM SafeWatch.AppCheck ac
INNER JOIN SafeWatch.AppCheckItem aci ON aci.Id = ac.AppCheckItemId
LEFT JOIN SafeWatch.MissionInvalidItem mii ON mii.AppCheckId = ac.Id
WHERE ac.CheckResult=0
GO

 

3、IF EXISTS ( SELECT *

FROM sys.objects
WHERE object_id = OBJECT_ID(N'[register].[EmploymentAllowanceRosterArchiveView]')
AND type IN ( N'V' ) )
DROP VIEW [register].[EmploymentAllowanceRosterArchiveView]
GO

 

4、添加外键约束

ALTER TABLE dbo.PreparednessDocument_CheckItem_Mapping ADD CONSTRAINT fk_CheckItem_PreparednessDocument_CheckItem_Mapping
FOREIGN KEY(CheckItem_Id) REFERENCES dbo.CheckItem(Id)
go