判断sql对象是否存在，不存在就创建

--创建主键
IF NOT EXISTS (select name from dbo.sysobjects where xtype='PK' and parent_obj=(select id from dbo.sysobjects where name='Employee'))
BEGIN
ALTER TABLE Employee ADD PRIMARY KEY(Id);
END
GO

--创建外键

IF NOT EXISTS(SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE CONSTRAINT_NAME='FK_Employee_Department')
BEGIN
ALTER TABLE [dbo].[Employee] WITH CHECK ADD CONSTRAINT [FK_Employee_Department] FOREIGN KEY([DptID])
REFERENCES [dbo].[Department] ([DptID])
ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_Employee_Department]
END
GO

--创建非聚集索引
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name='NonClusteredIndex_Employee_Name')
BEGIN
CREATE INDEX NonClusteredIndex_Employee_Name ON Employee(Name)
END
GO

--添加列
IF NOT EXISTS(SELECT * FROM SYSCOLUMNS WHERE id=OBJECT_ID('Employee') and name='Name')
BEGIN
ALTER TABLE Employee ADD Name VARCHAR(20)
END
GO

--创建触发器，当语文分数变化时，修改学生的总分数,其中inserted表示语文分数变化的数据行的集合
IF NOT EXISTS(SELECT * FROM SYSOBJECTS WHERE TYPE='tr' and name='Trigger_Student_TotalScore')
BEGIN
EXECUTE ('CREATE TRIGGER [dbo].[Trigger_Student_TotalScore]
ON [dbo].[Student] AFTER UPDATE
AS IF UPDATE(ChineseScore)
BEGIN
UPDATE Student SET TotalScore=ChineseScore+MathScore WHERE ID=(SELECT ID FROM inserted)
END')
END

 

//创建默认值

IF(SELECT default_object_id FROM sys.columns WHERE object_id = OBJECT_ID('Employee') AND name='UserNo')=0
BEGIN
ALTER TABLE [dbo].[Employee] ADD DEFAULT ((0)) FOR [UserNo]
END
GO

//创建视图，先删除再创建可以达到更新的目的

IF EXISTS(SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'vStudents'))
BEGIN
DROP VIEW [dbo].[vStudents]
END
GO

CREATE view [dbo].[vStudents]
as
SELECT Name,Age FROM Students
GO