--库是否存在,如果不存在则添加,如果存在则先删除,再添加;feiyan(数据库名称)
IF NOT EXISTS (SELECT * FROM master..sysdatabases WHERE name=N'feiyan')
BEGIN
CREATE DATABASE feiyan
END
ELSE
BEGIN
DROP DATABASE feiyan
CREATE DATABASE feiyan
END
--架构是否存在,如果不存在则添加
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'OA')
EXEC sys.sp_executesql N'CREATE SCHEMA [OA] AUTHORIZATION [dbo]'
GO
-- 判断要创建的表是否存在,如果不存在则添加,如果存在则先删除,再添加;feiyan(表名)
IF OBJECT_ID (N'dbo.feiyan', N'table') IS NULL
--if (select object_id( 'feiyan ')) is null (对表来说这样也可以)
BEGIN
CREATE TABLE feiyan (xm NVARCHAR(6))
END
ELSE BEGIN
DROP TABLE feiyan
CREATE TABLE feiyan (xm NVARCHAR(6))
END
--判断一个表的某个列是否存在,如果不存在则添加,如果存在则先删除,再添加;feiyan(表名),xm(列名)
IF COL_LENGTH( 'feiyan ', 'xm') IS NULL
BEGIN
ALTER TABLE feiyan ADD xm NVARCHAR(6)
EXECUTE sp_addextendedproperty N'MS_Description', N'备注', N'SCHEMA', N'架构', N'TABLE', N'表名', N'COLUMN', N'列名'
END
ELSE
BEGIN
ALTER TABLE feiyan DROP COLUMN xm
ALTER TABLE feiyan ADD xm NVARCHAR(6)
END
-- 判断要创建的存储过程名是否存在,如果不存在则添加,如果存在则先删除,再添加;MyProcedure(存储过程名)
IF OBJECT_ID (N'dbo.MyProcedure', N'PROCEDURE') IS NULL
BEGIN
DECLARE @sql VARCHAR(8000)
SET @sql = 'CREATE PROCEDURE MYPROCEDURE AS SELECT GETDATE()'
EXECUTE(@sql)
END
ELSE
BEGIN
DROP PROCEDURE MyProcedure
SET @sql = 'CREATE PROCEDURE MYPROCEDURE AS SELECT GETDATE()'
EXECUTE(@sql)
END
-- 判断要创建的视图名是否存在,如果不存在则添加,如果存在则先删除,再添加;MyView(视图名),feiyan(表名)
IF OBJECT_ID (N'dbo.MyView', N'VIEW') IS NULL
BEGIN
DECLARE @sql VARCHAR(8000)
SET @sql='CREATE VIEW MyView AS SELECT xm FROM feiyan'
EXECUTE(@sql)
END
ELSE
BEGIN
DROP VIEW MyView
SET @sql='create view MyView as select xm from feiyan'
EXECUTE(@sql)
END
-- 判断要创建的函数名是否存在,如果不存在则添加,如果存在则先删除,再添加;myfunction(函数名)
IF OBJECT_ID (N'dbo.myfunction', N'FN') IS NULL
BEGIN
DECLARE @sql VARCHAR(8000)
SET @sql='Create FUNCTION MyFunction() RETURNS int as begin return 1 end'
EXECUTE(@sql)
END
ELSE
BEGIN
DROP FUNCTION myfunction
SET @sql='Create FUNCTION MyFunction() RETURNS int as begin return 1 end'
EXECUTE(@sql)
END
--判断字段说明
SELECT
表名=case when a.colorder=1 then d.name else '' end,
表说明=case when a.colorder=1 then isnull(f.value,'') else '' end,
字段序号=a.colorder,
字段名=a.name,
标识=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
))) then '√' else '' end,
类型=b.name,
占用字节数=a.length,
长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允许空=case when a.isnullable=1 then '√'else '' end,
默认值=isnull(e.text,''),
字段说明=isnull(g.[value],'')
FROM syscolumns a
left join systypes b on a.xusertype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id
left join sys.extended_properties f on d.id=f.major_id and f.minor_id=0
--where d.name='orders' --如果只查询指定表,加上此条件
order by a.id,a.colorder
---判断要添加的索引是否存在
USE [LisMain]
GO
/****** Object: Index [PK_MOULD_MAIN_ID] Script Date: 08/12/2015 18:30:34 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[LisBase].[REQ_MOULD_DETAIL]') AND name = N'IX_MOULD_MAIN_ID')---IX非聚集索引、PK聚集索引
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE NONCLUSTERED INDEX [IX_MOULD_MAIN_ID] ON [LisBase].[REQ_MOULD_DETAIL]
(
[MOULD_MAIN_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]'
END
GO
---添加字段描述
USE LisMain
GO
IF COL_LENGTH('REAG.REAGENTDICT','EXT5') IS NOT NULL
BEGIN
EXECUTE sp_dropextendedproperty 'MS_Description','SCHEMA','REAG','table','REAGENTDICT','column','EXT5'
EXECUTE sp_addextendedproperty N'MS_Description', '可交易编码', N'SCHEMA', N'REAG', N'table', N'REAGENTDICT', N'column', N'EXT5'
--EXECUTE sp_updateextendedproperty N'MS_Description', '可交易编码', N'SCHEMA', N'REAG', N'table', N'REAGENTDICT', N'column', N'EXT5' --修改字段说明
END
GO
---添加主键
USE LisMain
GO
IF NOT EXISTS (SELECT * from sys.key_constraints where parent_object_id=object_id('LisBase.CHARGE_MOULD_MAIN') and type='PK')
BEGIN
ALTER TABLE LisBase.CHARGE_MOULD_MAIN ADD CONSTRAINT
PK_CHARGE_MOULD_MAIN PRIMARY KEY CLUSTERED
(
ID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END
GO
---设置列不为空,设置默认值
USE LisMain
GO
IF COL_LENGTH('LisBase.Sys_DicItem','Memo1') IS NOT NULL
BEGIN
ALTER TABLE LisBase.Sys_DicItem ALTER COLUMN Memo1 VARCHAR(50) NOT NULL
ALTER TABLE LisBase.Sys_DicItem ADD CONSTRAINT DF_Sys_DicItem_Memo1 DEFAULT('0') FOR Memo1
END
领取专属 10元无门槛券
私享最新 技术干货