当我们对某个DB中所有表加上一个字段后,现在发现其实并不需要这个字段,那么我们再次用循环将此字段删除。
一、删除某个字段的SQL语句
ALTER TABLE tablename DROP COLUMN columnname
二、删除某个字段时,报错。
ALTER TABLE tablename DROP COLUMN columname failed because one or more objects access this column.
这时候我们首先应该删除constraint,再删除此字段,如下:
ALTER TABLE tablename DROP CONSTRAINT DF_tablename_columnname
ALTER TABLE tablename DROP COLUMN columnname
三、如果constraint不存在,执行SQL语句就会报错,如何判断constraint是否存在呢?
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table AND COLUMN_NAME='mycolumnname' AND COLUMN_DEFAULT IS NOT NULL)
BEGIN
EXEC ('alter table ' + @table + ' DROP CONSTRAINT DF_' + @table + '_mycolumnname')
END
这样就会在constraint存在的情况下,将它删除了.
四、如果要删除的字段不存在,执行删除字段的SQL语句也会报错,如何判断此字段是否存在呢?
if Exists(select * from sys.columns where Name = N'mycolumnname' and Object_ID = Object_ID(@table))
begin
EXEC ('ALTER TABLE ' + @table + ' DROP COLUMN mycolumnname')
end
五、完整SQL语句如下:
关于代码其他部分可参考某个DB中所有表加上一个字段
use databasename
go
declare tnames_cursor CURSOR
FOR
select name from sysobjects where xtype='u'
OPEN tnames_cursor
DECLARE @table sysname
FETCH NEXT FROM tnames_cursor INTO @table
while(@@FETCH_STATUS <> -1)
begin
IF(@@FETCH_STATUS <> -2)
BEGIN
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table AND COLUMN_NAME='mycolumnname' AND COLUMN_DEFAULT IS NOT NULL)
BEGIN
EXEC ('alter table ' + @table + ' DROP CONSTRAINT DF_' + @table + '_mycolumnname')
END
if Exists(select * from sys.columns where Name = N'mycolumnname' and Object_ID = Object_ID(@table))
begin
EXEC ('ALTER TABLE ' + @table + ' DROP COLUMN mycolumnname')
end
print @table
END
FETCH NEXT FROM tnames_cursor INTO @table
end
CLOSE tnames_cursor
DEALLOCATE tnames_cursor
六、同时传递多个参数
上面的实例,我们只传入了一个参数,这种情况最普遍。但我们也会经常碰到同时需要好几个字段作为参数的情况,那应该怎么办呢?
大道理是一致的,只是小部分不同。参看下面代码就明白了。
declare cursor1 CURSOR
FOR
select col1,col2, col3 from #tmpTable order by col1
OPEN cursor1
DECLARE @col1 float
declare @col2 float
declare @col3 nvarchar(15)
FETCH NEXT FROM cursor1 INTO @col1, @col2, @col3
while(@@FETCH_STATUS <> -1)
begin
IF(@@FETCH_STATUS <> -2)
BEGIN
if @col1 is not null
begin
print '@col1, @col2, @col3 =' + @col1 + @col2 + @col3
end
END
FETCH NEXT FROM bacc_cursor INTO @col1, @col2, @col3
end
CLOSE cursor1
DEALLOCATE cursor1