--给你这个,取得一个表的字段列表,然后把不需要的列去掉。 declare @sql varchar(1000) set @sql='' select @sql=@sql+name+',' from syscolumns where id=(select id from sysobjects where name='tablename') order by colid set @sql=left(@sql,len(@sql)-1) print @sql
LZ和我一样懒, 提供一个函数给你吧, 根据表名, 列出所有的字段, 至于想排除某列的话, 删除那个字段就行了 调用起来很方便, 如select dbo.fn_GetField('sysobjects') select dbo.fn_GetField('Table') select dbo.fn_GetField('LZ的表名') 函数体如下if( object_id('fn_GetField') is not null ) drop function fn_GetField go create function fn_GetField( @cTblName varchar(80) ) returns varchar(2000) as begin declare @cSQL varchar(1000) set @cSQL='' select @cSQL = @cSQL + name+', ' from syscolumns where id = object_id(@cTblName) order by colid set @cSQL = 'select ' + left( @cSQL, len(@cSQL)-1 ) + ' from ' + @cTblName return( @cSQL ) end go
想想也有简单的语句 select * into #tmp from xxxx alter table #tmp drop column YYYY select * from #tmp
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+name+',' from syscolumns where id=(select id from sysobjects where name='tablename') order by colid
set @sql=left(@sql,len(@sql)-1)
print @sql
调用起来很方便, 如select dbo.fn_GetField('sysobjects')
select dbo.fn_GetField('Table')
select dbo.fn_GetField('LZ的表名')
函数体如下if( object_id('fn_GetField') is not null )
drop function fn_GetField
go
create function fn_GetField( @cTblName varchar(80) )
returns varchar(2000)
as
begin
declare @cSQL varchar(1000)
set @cSQL=''
select @cSQL = @cSQL + name+', '
from syscolumns
where id = object_id(@cTblName)
order by colid
set @cSQL = 'select ' + left( @cSQL, len(@cSQL)-1 ) + ' from ' + @cTblName
return( @cSQL )
end
go
select * into #tmp from xxxx
alter table #tmp drop column YYYY
select * from #tmp