如何同时判断表是否存在以及字段是否存在

[niuzhouhai]

我想要删除某个字段,sql语句如下

IF EXISTS(select ObjectProperty(Object_ID( 'students'),'IsUserTable')) ALTER TABLE students DROP COLUMN status

但这个语句只能判断表是否存在,并未判断表下面的字段是否存在.结果导致我在执行批量语句的时候总是中断.如何在以上语句中在加入字段是否存在的判断呢?

[道玄希言]

好像有个 col_length 函数。

select case col_length('students','status') when null then 0 else 1 end

[zbdzjx]

SELECT * FROM SYS.all_columns 
where OBJECT_ID =(SELECT object_id FROM SYS.all_objects WHERE name='a000')
and name='id'

其中的a000是表名，id是列名。

[阳泉酒家小当家]

IF EXISTS(select * from sys.tables where name ='students') and 
   exists(select * from sys.columns where object_id = object_id('students') and name = 'status')
   ALTER TABLE students DROP COLUMN status

[道玄希言]

IF (EXISTS(select ObjectProperty(Object_ID( 'students'),'IsUserTable')) 
and (EXISTS(select case col_length('students','[status]') when null then 0 else 1 end)))
ALTER TABLE students DROP COLUMN [status]

[niuzhouhai]

引用 2 楼 yupeigu 的回复:

IF EXISTS(select * from sys.tables where name ='students') and 
   exists(select * from sys.columns where object_id = object_id('students') and name = 'status')
   ALTER TABLE students DROP COLUMN status

试过可以用,谢谢,我原来不知道 两个EXISTS之间可以用and连起来

[niuzhouhai]

引用 4 楼 yangb0803 的回复:

IF (EXISTS(select ObjectProperty(Object_ID( 'students'),'IsUserTable')) 
and (EXISTS(select case col_length('students','[status]') when null then 0 else 1 end)))
ALTER TABLE students DROP COLUMN [status]

表中是否存在列的判断有误,2楼那个我试过了可以用