select * from syscolumns where id=(select id from sysobjects where name ='table1') and name not in ('col2','col40')
declare @varField varchar(8000)select @varField=isnull(@varField,'')+name+',' where id=(select id from sysobjects where name ='table1') and name not in ('col2','col40') --select @varField set @varField='select ' + left(@varField,len(@varField)-1) + ' from item ' --select @varField exec(@varField)
declare @s varchar(8000) st @s = ''select @s = @s + ',['+name+']' from syscolumns where id=object_id('Table1') and colid not in(2,40) order by colidset @s = 'select '+stuff(@s,1,1,'')+' from table1'exec(@s)
回复人: libin_ftsafe(子陌红尘|潇湘剑公子@dev-club) ( ) 信誉:105 2005-12-09 11:04:00 得分: 0 declare @s varchar(8000) st @s = ''select @s = @s + ',['+name+']' from syscolumns where id=object_id('Table1') and colid not in(2,40) order by colidset @s = 'select '+stuff(@s,1,1,'')+' from table1'exec(@s)我还想问,我是在Delphi里用TQuery做查询,应该怎么组织语句啊?我要对每一条记录做操作。
回复人: wutao411(了缘) ( ) 信誉:100 SqlStr = 'select name from syscolumns where id=(select id from sysobjects where name =''table1'') and name not in (''col2'',''col40'')';SelStr := '';Query1.Close; Query1.Sql.Clear; Query1.Sql.Add(SqlStr); Query1.Open;while not Query1.Eof do SelStr := SelStr + Query1.FieldByName('Name').AsString + ',';Delete(SelStr, Length(SelStr)-1, 1);SqlStr := 'Select ' + SelStr + ' From Table1 '; Query1.Close; Query1.Sql.Clear; Query1.Sql.Add(SqlStr); Query1.Open; 这里name不能取得值,因为它是sysname类型,所以我要搞个usercolumns和userobjects,同系统的相同字段,只是name的类型改为varchar(255),但是又有问题出现,怎么让usercolumns和syscolumns同步,userobjects和sysobjects同步,因为系统表不支持触发器。
创建存储过程,供Delphi程序调用: -------------------------------------------------------------- create procedure sp_getResult as begin declare @s varchar(8000) set @s = ''
select @s = @s + ',['+name+']' from syscolumns where id=object_id('Table1') and colid not in(2,40) order by colid
set @s = 'select '+stuff(@s,1,1,'')+' from table1'
exec(@s) end go--在查询分析器中调用 exec sp_getResult
var SqlStr, SelStr:String; begin Query_Data.RequestLive := false; SqlStr := 'select usercolumns.name from usercolumns,userobjects where usercolumns.id=userobjects.id and userobjects.name =''基本信息表'' and usercolumns.name not in (''所在支行'',''身份证号'')';SelStr := '';Query_Data.Close; Query_Data.Sql.Text := SqlStr; Query_Data.Open;while not Query_Data.Eof do begin SelStr := SelStr + Query_Data.FieldByName('Name').AsString + ','; Query_Data.Next; end;Delete(SelStr, Length(SelStr), 1);SqlStr := 'Select ' + SelStr + ' From 基本信息表 '; Query_Data.Close; Query_Data.Sql.Text := SqlStr; Query_Data.Open; 刚才试了一下,这样可以。
--select @varField
set @varField='select ' + left(@varField,len(@varField)-1) + ' from item '
--select @varField
exec(@varField)
st @s = ''select
@s = @s + ',['+name+']'
from
syscolumns
where
id=object_id('Table1') and colid not in(2,40)
order by
colidset @s = 'select '+stuff(@s,1,1,'')+' from table1'exec(@s)
declare @s varchar(8000)
st @s = ''select
@s = @s + ',['+name+']'
from
syscolumns
where
id=object_id('Table1') and colid not in(2,40)
order by
colidset @s = 'select '+stuff(@s,1,1,'')+' from table1'exec(@s)我还想问,我是在Delphi里用TQuery做查询,应该怎么组织语句啊?我要对每一条记录做操作。
SqlStr = 'select name from syscolumns where id=(select id from sysobjects where name =''table1'') and name not in (''col2'',''col40'')';SelStr := '';Query1.Close;
Query1.Sql.Clear;
Query1.Sql.Add(SqlStr);
Query1.Open;while not Query1.Eof do
SelStr := SelStr + Query1.FieldByName('Name').AsString + ',';Delete(SelStr, Length(SelStr)-1, 1);SqlStr := 'Select ' + SelStr + ' From Table1 ';
Query1.Close;
Query1.Sql.Clear;
Query1.Sql.Add(SqlStr);
Query1.Open;
这里name不能取得值,因为它是sysname类型,所以我要搞个usercolumns和userobjects,同系统的相同字段,只是name的类型改为varchar(255),但是又有问题出现,怎么让usercolumns和syscolumns同步,userobjects和sysobjects同步,因为系统表不支持触发器。
--------------------------------------------------------------
create procedure sp_getResult
as
begin
declare @s varchar(8000)
set @s = ''
select
@s = @s + ',['+name+']'
from
syscolumns
where
id=object_id('Table1') and colid not in(2,40)
order by
colid
set @s = 'select '+stuff(@s,1,1,'')+' from table1'
exec(@s)
end
go--在查询分析器中调用
exec sp_getResult
SqlStr, SelStr:String;
begin
Query_Data.RequestLive := false;
SqlStr := 'select usercolumns.name from usercolumns,userobjects where usercolumns.id=userobjects.id and userobjects.name =''基本信息表'' and usercolumns.name not in (''所在支行'',''身份证号'')';SelStr := '';Query_Data.Close;
Query_Data.Sql.Text := SqlStr;
Query_Data.Open;while not Query_Data.Eof do
begin
SelStr := SelStr + Query_Data.FieldByName('Name').AsString + ',';
Query_Data.Next;
end;Delete(SelStr, Length(SelStr), 1);SqlStr := 'Select ' + SelStr + ' From 基本信息表 ';
Query_Data.Close;
Query_Data.Sql.Text := SqlStr;
Query_Data.Open;
刚才试了一下,这样可以。