SQL SERVER对表、视图等的判断

--库是否存在，如果不存在则添加,如果存在则先删除,再添加;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