select name from syscolumns where id=(select id from sysobjects where name=表名) order by colid 这将“表名”指定表的所有字段,以字段的创建顺序排列。以这个为基础生成一段SQL,应该不难。 declare @sql varchar(8000) select @sql='select ' select @sql=@sql+name+',' from (select colid,[name] from syscolumns where id=(select id from sysobjects where name='表名') and colid >1) a select @sql=left(@sql,len(@sql) - 1)+' from 表名' execute(@sql)
declare @str varchar(8000) set @str = '' select @str = @str + ',' + name from syscolumns where id = object_id('表名称') and colid <>1 order by colid set @str = 'select ' + stuff(@str,1,1,'') + ' from 表名称' EXEC(@str)
注意上面的方法有个小问题就是如果你有删除过的列的话,colid有可能没有1的,比如你本身表只有一列,你删了,然后再添加了3列,这时候colid应该是2,3,4如果知道列的名字也可以where name not in...create table test (col1 int identity(1,1), col2 varchar(10), col3 char(2), col4 datetime) select * from testdeclare @sql varchar(8000) set @sql='' select @sql=@sql+','+[name] from (select [name] from syscolumns where [id]=object_id('test') and [name] not in ('col2','col3'))a set @sql='select '+stuff(@sql,1,1,'')+' from test' exec(@sql)drop table test --------------------------------------- col1 col2 col3 col4 ----------- ---------- ---- ----------col1 col4 ----------- -----------------
create function fun_chggxhfield(@kind int=2) returns varchar(8000) as begin declare @s1 varchar(8000) set @s1='' if @kind=0 select @s1=@s1+',['+name+']' from syscolumns where object_id('表名')=id and name not in('不需要的字段名') order by colid if left(@s1,1)=',' set @s1=stuff(@s1,1,1,'') return (isnull(@s1,'')) end
这将“表名”指定表的所有字段,以字段的创建顺序排列。以这个为基础生成一段SQL,应该不难。
declare @sql varchar(8000)
select @sql='select '
select @sql=@sql+name+','
from (select colid,[name] from syscolumns where id=(select id from sysobjects where name='表名') and colid >1) a
select @sql=left(@sql,len(@sql) - 1)+' from 表名'
execute(@sql)
set @str = ''
select @str = @str + ',' + name from syscolumns
where id = object_id('表名称') and colid <>1 order by colid
set @str = 'select ' + stuff(@str,1,1,'') + ' from 表名称'
EXEC(@str)
(col1 int identity(1,1),
col2 varchar(10),
col3 char(2),
col4 datetime)
select * from testdeclare @sql varchar(8000)
set @sql=''
select @sql=@sql+','+[name] from
(select [name] from syscolumns where [id]=object_id('test') and [name] not in
('col2','col3'))a
set @sql='select '+stuff(@sql,1,1,'')+' from test'
exec(@sql)drop table test
---------------------------------------
col1 col2 col3 col4
----------- ---------- ---- ----------col1 col4
----------- -----------------
returns varchar(8000)
as
begin
declare @s1 varchar(8000)
set @s1=''
if @kind=0 select @s1=@s1+',['+name+']' from syscolumns where object_id('表名')=id and name not in('不需要的字段名') order by colid
if left(@s1,1)=',' set @s1=stuff(@s1,1,1,'')
return (isnull(@s1,''))
end