多看看以前的贴子,这是我在 http://expert.csdn.net/Expert/topic/1652/1652175.xml?temp=.6248133 写的使用动态SQL来生成吧 declare @sql varchar(3000) select @sql='' select @sql=@sql+',['+name+']' from syscolumns where id=object_id('tablename') and name not in('field1','field2') select @sql='select '+right(@sql,len(@sql)-1)+' from tablename' exec(@sql)tablename为你要查询的表名,field1,field2为你要排除的两个字段.
假设表中一共10个字段 declare @Col as varchar(20) --@col为排除的字段名 declare @StrSql as varchar(500) --完整的Sql declare @StrSqlResult as varchar(500) --最终的Sql set @StrSql="select a1,a2,a3,a4,a5,a6,a7,a8,a9,a10 from table1" case when @Col="a1" then @StrSqlResult=replace(strsql,@Col,"") else @StrSqlResult=replace(strsql,","+@Col,"") end exec(@StrSqlResult)
这个不用动态SQL也好办:select 某一列 into #t from tab drop table tab select * into tab from #t
http://expert.csdn.net/Expert/topic/1652/1652175.xml?temp=.6248133
写的使用动态SQL来生成吧
declare @sql varchar(3000)
select @sql=''
select @sql=@sql+',['+name+']'
from syscolumns
where id=object_id('tablename')
and name not in('field1','field2')
select @sql='select '+right(@sql,len(@sql)-1)+' from tablename'
exec(@sql)tablename为你要查询的表名,field1,field2为你要排除的两个字段.
declare @Col as varchar(20) --@col为排除的字段名
declare @StrSql as varchar(500) --完整的Sql
declare @StrSqlResult as varchar(500) --最终的Sql
set @StrSql="select a1,a2,a3,a4,a5,a6,a7,a8,a9,a10 from table1"
case when @Col="a1" then
@StrSqlResult=replace(strsql,@Col,"")
else
@StrSqlResult=replace(strsql,","+@Col,"")
end
exec(@StrSqlResult)
drop table tab
select * into tab from #t