SQL的各种判断

删除带约束(默认值)的表的某一列

--获取字段上面的约束名称,组合成一句字符串,用exec执行

IF exists(SELECT  1 FROM sysobjects  where id IN (SELECT cdefault FROM syscolumns where name='[列名]' AND  id=object_id('[表名]'))) 
begin
declare @name varchar(100) 
SELECT  @name=name FROM sysobjects  where id IN (SELECT cdefault FROM syscolumns where name='[列名]' AND  id=object_id('[表名]'))
exec ('alter table [表名] drop constraint '+@name) 
end
--删除列
if exists(select 1 from syscolumns where id=object_id('[表名]') and name='[列名]')
alter table [表名] drop column [列名]

--判断某列是否存在默认值,不存在则添加默认值
if not  exists (select A.name as DefaultName,B.name as TableName,* from sysobjects A
inner join sysobjects B on A.parent_obj = B.id
INNER join syscolumns C on B.id=object_id('[表名]') AND  C.name='[列名]'
where A.xtype = 'D' and B.xtype = 'U' and B.name = '[表名]')
ALTER table [表名]
ADD DEFAULT (getdate()) FOR [列名] 

数据库是否存在if exists(select * from master..sysdatabases where name=N'库名')print 'exists'elseprint 'not exists'
判断要创建的表名是否存在if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[表名]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
 删除表drop table [dbo].[表名]GO
判断要创建临时表是否存在If Object_Id('Tempdb.dbo.#Test') Is Not NullBeginprint '存在'EndElseBeginprint '不存在'End
判断要创建的存储过程名是否存在if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[存储过程名]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
删除存储过程drop procedure [dbo].[存储过程名]GO
判断要创建的视图名是否存在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].[函数名]GOif col_length('表名', '列名') is nullprint '不存在'IF not exists(SELECT 1 from syscolumns where name='列名' AND id=object_id('表名'))