删除字段_Salary ALTER TABLE Employees DROP COLUMN [_Salary]
if exists(select * from employees where charindex('_',salary)>0) alter table employees drop column salary
if object_id('test') is not null drop table test select 1 as _aaaa, 2 as bb_bb, 3 as cccc_, 4 as dddd into test go -------------------------------------------------------- declare @s varchar(800) set @s = '' select @s = @s + [name] + ',' from syscolumns where [id] = object_id('test') and [name] like '%[_]%' if @s <> '' begin set @s = 'alter table test drop column ' + left(@s, len(@s) - 1) exec(@s) end -------------------------------------------------------- select * from test go drop table test
if object_id('test') is not null drop table test select 1 as _aaaa, 2 as bb_bb, 3 as cccc_, 4 as dddd into test go -------------------------------------------------------- declare @s varchar(800) set @s = '' select @s = @s + [name] + ',' from syscolumns where [id] = object_id('test') and [name] like '%[_]%' if @s <> '' begin set @s = 'alter table test drop column ' + left(@s, len(@s) - 1) exec(@s) end -------------------------------------------------------- select * from test /* dddd 4 */ go drop table test
declare @name varchar(100),@n varchar(100)declare cur CURSOR for select 'fs.'+ name,replace(name,'_','') as n from syscolumns WHERE id = object_id('fs') AND charindex('_',name)>0 open cur fetch next from cur into @name,@n while @@fetch_status = 0 beginEXEC sp_rename @name,@n, 'COLUMN' fetch next from cur into @name,@n end
ALTER TABLE Employees DROP COLUMN [_Salary]
alter table employees drop column salary
select 1 as _aaaa, 2 as bb_bb, 3 as cccc_, 4 as dddd
into test
go
--------------------------------------------------------
declare @s varchar(800)
set @s = ''
select @s = @s + [name] + ',' from syscolumns where [id] = object_id('test') and [name] like '%[_]%'
if @s <> ''
begin
set @s = 'alter table test drop column ' + left(@s, len(@s) - 1)
exec(@s)
end
--------------------------------------------------------
select * from test
go
drop table test
select 1 as _aaaa, 2 as bb_bb, 3 as cccc_, 4 as dddd
into test
go
--------------------------------------------------------
declare @s varchar(800)
set @s = ''
select @s = @s + [name] + ',' from syscolumns where [id] = object_id('test') and [name] like '%[_]%'
if @s <> ''
begin
set @s = 'alter table test drop column ' + left(@s, len(@s) - 1)
exec(@s)
end
--------------------------------------------------------
select * from test
/*
dddd
4
*/
go
drop table test
open cur
fetch next from cur into @name,@n
while @@fetch_status = 0
beginEXEC sp_rename @name,@n, 'COLUMN'
fetch next from cur into @name,@n
end
close cur
deallocate cur
EXECUTE sp_rename N'dbo.t.c4', N'Tmp__c4', 'COLUMN'
GO
EXECUTE sp_rename N'dbo.t.Tmp__c4', N'_c4', 'COLUMN'