MS sql 判断数据库、表、函数、存储过程是否存在

-- 库是否存在
if exists(select * from master..sysdatabases where name=N'库名')
print 'exists'
else
print 'not exists'
  

-- 判断要创建的表名是否存在
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[表名]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
-- 删除表
drop table [dbo].[表名]
GO

--判断字段是否存在，添加字段ManageMechanism_Id    -2008
IF NOT EXISTS (SELECT * FROM syscolumns 
	WHERE [name] = 'ManageMechanism_Id' AND id = (SELECT TOP 1 id 
		FROM sysobjects WHERE [name] = '表名'))
ALTER TABLE 表名
	ADD [ManageMechanism_Id] udtGuid NULL 
GO

--判断约束是否存在 
--FK_HAS_ACCI_REFERENCE_HAS_ACCIQUI 为约束名称
if exists (select 1 from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F') 
           where r.fkeyid = object_id('表名') and o.name = 'FK_HAS_ACCI_REFERENCE_HAS_ACCIQUI')
    alter table 表名 drop constraint FK_HAS_ACCI_REFERENCE_HAS_ACCIQUI
go
--添加约束alter table 表名 
add constraint FK_HAS_ACCI_REFERENCE_HAS_ACCIQUI foreign key (AccidentQuickReport_Id)
    references 另外的关联表名 (AccidentQuickReport_Id) on delete cascadego
-- 判断要创建的视图名是否存在
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[视图名]') and OBJECTPROPERTY(id, N'IsView') = 1)
-- 删除视图
drop view [dbo].[视图名]
GO



-- 判断要创建的函数名是否存在
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[函数名]') and xtype in (N'FN', N'IF', N'TF'))
-- 删除函数
drop function [dbo].[函数名]
GO 

if exists (select * from sysobjects where xtype='fn' and name='函数名')

--2008
IF OBJECT_ID('[函数名]')  IS NOT NULL
    DROP  function [dbo].[函数名]
GO


if col_length('表名', '列名') is null
print '不存在'
select 1 from sysobjects where id in (select id from syscolumns where name='列名') and name='表名'

--除法显示小数 结果为0.0178772749234981
select (cast(111 as float)/cast(datediff(dd, '2000-3-01','2017-3-01') as float))
--除法，小数部分不显示 结果为0
select (cast(111 as float)/cast(datediff(dd, '2000-3-01','2017-3-01') as float))

--除法显示小数 结果为0.0179 保留两位有效数字。
--如 Round(2.3344,2) 则 会在把有效数字后面的 变为0 ，成 2.3300。
select round((cast(111 as float)/cast(datediff(dd, '2000-3-01','2017-3-01') as float)),4)
--除法显示小数 结果为0.0179 保留两位有效数字。
select Convert(decimal(18,4),(cast(111 as float)/cast(datediff(dd, '2000-3-01','2017-3-01') as float)))