SQL_Create相关操作

--1、创建数据库的判断
--1、if not exists(select 1 from sysdatabases where name = 'dbname')
drop database dbname

go

CREATE DATABASE Test
ON PRIMARY
(
NAME='Test',
FILENAME='路径名\Test.mdf',
SIZE=10MB,
MAXSIZE=1GB,
FILEGROWTH=1mb
)

--2、创建表前判断是否存在，如果不存在就删除表重新创建
IF  exists (select 1 from sysobjects where type='U' And name='Student3')
drop table 表名

 create table 表名

(

    --语句块

)

--2.1 创建表前判断是否存在，

IF NOT EXISTS(SELECT * FROM SYSOBJECTS WHERE name ='ConfirmMessageInfo')
CREATE TABLE [dbo].[ConfirmMessageInfo](
[ModuelName] [varchar](50) NOT NULL,
[ID] [bigint] NOT NULL,
[EmpID] [varchar](20) NOT NULL)

 

 

--3.1.创建列前判断是否存在
IF NOT EXISTS(SELECT 1 FROM SYSCOLUMNS WHERE ID=OBJECT_ID('PerCompany') AND Name='port')
begin
alter table PerCompany add port varchar(5);
end
GO

--3.2.创建列前判断是否存在
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='peremployee' AND COLUMN_NAME='SyncToYgzjDate')
alter table peremployee add SyncToYgzjDate datetime null;

 

--4、创建存储过程的判断
if not exists (select 1 from sysobjects where name='sp_Add' And xtype='p')

go
create proc porc_add
as
begin

--语句块
end

 

--5、创建触发器的判断
if not exists (select 1 from sysobjects where name='tr_Tri' and xtype='tr')
drop trigger tr_Tri

go
create trigger tr_Tri

on 表名

for del操作
as
begin

 --语句块
end

--6、创建视图的判断
if not exists （select 1 from sysobjects where name='v_View' and xtype='v'）
drop view v_View

go
create view v_View
as
begin

--语句块
end

--7.1、创建自定义函数的判断

IF EXISTS (SELECT 1 FROM sysobjects WHERE xtype='fn' AND name='TestFN1')
DROP FUNCTION TestFN1
GO
create function TestFN1(@width int , @height int)
returns int
as
begin
return @width * @height
end

--7.2、创建函数前的判断

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_GetEmpIDListInRange_Boolean]') and xtype in (N'FN', N'IF', N'TF'))
DROP FUNCTION f_GetEmpIDListInRange_Boolean
GO
CREATE FUNCTION [dbo].[f_GetEmpIDListInRange_Boolean](@RangeXML NTEXT, @InCumbency INTEGER, @YYMMDD DATETIME, @EmpID VARCHAR(10))
RETURNS @EmpIDTable TABLE ( EmpID VARCHAR(10))
AS
BEGIN
/*条件*/
END

 

 

 --8.1、创建索引前的判断

IF EXISTS (SELECT * FROM SYSINDEXES WHERE NAME='IX_TEST_TNAME')--检测是否已经存在IX_TEST_TNAME索引

DROP INDEX TEST.IX_TEST_TNAME--如果存在则删除
--创建索引
CREATE NONCLUSTERED INDEX IX_TEST_TNAME --创建一个非聚集索引
ON TEST(TNAME) --为TEST表的TNAME字段创建索引
WITH FILLFACTOR = 30 --填充因子为30%
GO
SELECT * FROM PerEmployee(INDEX = empid) WHERE EmpName = 'A' --指定按‘IX_TEST_TNAME’索引查询

 --8.2、创建索引前的判断

IF NOT EXISTS(select object_name(object_id) tableName,name,type_desc from sys.indexes where name='pelessontype_index')
create index pelessontype_index on PXLessonType(lessontypeNo,parentNo,id);
GO

--8.3、创建索引前的判断，增加任务反馈ID - 复合索引
IF NOT EXISTS(select object_name(object_id) tableName,name,type_desc from sys.indexes where name='taskId_feedbackId')
ALTER TABLE JKFeedbacWorkTask add constraint taskId_feedbackId unique(feedbackId,taskId)

 

--9.追加创建约束前的判断

IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID('DF__ConfirmMe__Modue__3E327A44') )
begin
ALTER TABLE [dbo].[ConfirmMessageInfo] ADD CONSTRAINT [DF__ConfirmMe__Modue__3E327A44] DEFAULT (NULL) FOR [ModuelName]
end
GO

--10.追加创建默认值前的判断
IF NOT EXISTS (SELECT name FROM sysobjects WHERE id = (SELECT syscolumns.cdefault FROM sysobjects INNER JOIN syscolumns ON sysobjects.id=syscolumns.id WHERE sys.sysobjects.name = 'EtPlanDayDataDtl' AND syscolumns.name ='ID' ))
begin
ALTER TABLE [dbo].[EtPlanDayDataDtl] ADD DEFAULT (NULL) FOR [ID]
END

--11.追加创建属性前的判断
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='work_lottery_participants' AND COLUMN_NAME='id')
begin
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'主键' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'work_lottery_participants', @level2type=N'COLUMN',@level2name=N'id'
end
GO

 

--12.插入数据前的判断
IF NOT EXISTS(SELECT * FROM Self_Sys_Menu WHERE id='160907121212120029')
INSERT [dbo].[Self_Sys_Menu] ([id], [platform_id], [parent_id], [fun_id], [name], [ename], [sort], [href], [target], [icon], [is_show], [permission], [version], [create_date], [create_by], [update_date], [update_by], [remarks], [del_flag])
VALUES (160907121212120029, 10000, 0, NULL, N'培训与学习', N'E-Learning', 7, NULL, NULL, N'images/xin/e_learn.png', 0, NULL, 0, CAST(0x0000A6AB00936C44 AS DateTime), N'Admin', NULL, NULL, NULL, 0)