第2个问题:
declare @CmdStr varchar(8000)
set @CmdStr=''
select @CmdStr=@CmdStr+
'alter table '+name+' drop column aa'+char(13)
from sysobjects where xtype='U'
execute @CmdStr
第3个问题:
declare @CmdStr varchar(8000)
set @CmdStr=''
select @CmdStr=@CmdStr+t2.name from sysobjects t1,syscolumns t2
where t1.id=t2.id and t1.name='你的表名' and t2.name not
in ('不可能的字段1','不可能的字段2')
set @CmdStr='select '+@CmdStr+ ' from 你的表名'
execute (@cmdstr)
declare @CmdStr varchar(8000)
set @CmdStr=''
select @CmdStr=@CmdStr+
'alter table '+name+' drop column aa'+char(13)
from sysobjects where xtype='U'
execute @CmdStr
第3个问题:
declare @CmdStr varchar(8000)
set @CmdStr=''
select @CmdStr=@CmdStr+t2.name from sysobjects t1,syscolumns t2
where t1.id=t2.id and t1.name='你的表名' and t2.name not
in ('不可能的字段1','不可能的字段2')
set @CmdStr='select '+@CmdStr+ ' from 你的表名'
execute (@cmdstr)
create Trigger AAA
on AAA after update
as
begin
--暂停一个触发器
exec('alter table 触发器所在的表 DISABLE TRIGGER 要暂停的触发器名')
.......
处理语句
.......
--启用触发器
exec('alter table 触发器所在的表 ENABLE TRIGGER 要暂停的触发器名')
end
go
2. 如何批量修改或刪除相似名字字段?declare @s varchar(8000)
select @s=@s+'
alter table 你的表 drop column ['+name+']'
from syscolumns
where object_id('你的表')=id and name like 'aa%'
exec(@s)
select not (aa, bb) from AAA, 將數據表中 非 aa, bb的字段列出來, SQL語句該如何寫呢??declare @s varchar(8000)
set @s=''
select @s=@s+',['+name+']'
from syscolumns
where object_id('你的表')=id and name not in('aa','bb')
set @s=substring(@s,2,8000)
exec('select '+@s+' from 你的表')
create Trigger AAA
on AAA after update
as
begin
atlter table aaa disabled Trigger BBB
.....
atlter table aaa enabled Trigger BBB
end
2, 批量修改或刪除相似名字字段create proc DropCmn(@cndt as varchar(10))--@cndt 就是以你的aa
begin
declare @sql varchar(8000)
set @sql='alter table aaa drop COLUMN '
select @sql=@sql+[name]+',' from syscolumns where [id]=object_id('aaa') and [name] like @cndt+'%'
set @sql=left(@sql,len(@sql)-1)
exec (@sql)
end3, select AA, BB from AAA 可以從數據表找出 AA, BB 列值, 現在我想的是, 用類似
select not (aa, bb) from AAA, 將數據表中 非 aa, bb的字段列出來, SQL語句該如何寫呢??
写法类型于2delcare @sql varchar(8000)
set @sql='select '
select @sql=@sql+[name]+',' from syscolumns where [id]=object_id('aaa') and [name] not in ('aa','bb')
set @sql=left(@sql,len(@sql)-1)
exec (@sql)