declare @list varchar(1000),@sql nvarchar(1000)
select @list=@list+','+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='table1'
set @sql='select '+right(@list,len(@list)-1)+' from table1'
exec (@sql)在网上看到这个SQL, 应该是返回某个表的所有字段, 但是上面的SQL执行没有东西返回, 怀疑是sql2000的写法.然后我就把它改成如下:declare @list varchar(1000),@sql nvarchar(1000)
select @list=b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='table1'
set @sql='select '+@list+' from table1'
exec (@sql)这样就有东西返回来, 但是意外的是只有一个age字段返回, 我的table1表里一共有3个字段: id, username, age请问一下这是什么原因..
select @list=@list+','+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='table1'
set @sql='select '+right(@list,len(@list)-1)+' from table1'
exec (@sql)在网上看到这个SQL, 应该是返回某个表的所有字段, 但是上面的SQL执行没有东西返回, 怀疑是sql2000的写法.然后我就把它改成如下:declare @list varchar(1000),@sql nvarchar(1000)
select @list=b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='table1'
set @sql='select '+@list+' from table1'
exec (@sql)这样就有东西返回来, 但是意外的是只有一个age字段返回, 我的table1表里一共有3个字段: id, username, age请问一下这是什么原因..
declare @list varchar(1000),
@Sql nvarchar(1000)
SET @list=''
select @list = @list +',' + b.name from sysobjects a
inner JOIN syscolumns b ON a.id=b.id
WHERE a.name='Table1'
SET @list = RIGHT(@list,LEN(@list)-1)
set @sql='select '+@list+' from Table1' exec (@sql)
try like this
declare @list varchar(1000),@sql nvarchar(1000)
select @list = ''
select @list=@list+','+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='table1'
set @sql='select '+right(@list,len(@list)-1)+' from table1' --PRINT @sql ---You can Output the string you want to execute first,you will find then there's nothing in the result grid,so the red part is the reason,because @list is null,null add anything also null.exec (@sql)
sorry,the red part doesn't show with the source code reference;declare @list varchar(1000),@sql nvarchar(1000)
select @list = ''
select @list=@list+','+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='table1'
set @sql='select '+right(@list,len(@list)-1)+' from table1'
--PRINT @sqlexec (@sql)
select @list=b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='table1'
set @sql='select '+@list+' from table1'
exec (@sql)
这样只能查询表中最后一例的数据
USE Northwind
declare @list varchar(1000),@sql nvarchar(1000)
SET @list = ''
select @list=@list+','+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='Customers'
set @sql='select '+ STUFF(@list,1,1,'') +' from Customers'
exec (@sql)