create table tb(id varchar(10))
insert into tb values('1')
insert into tb values('2')
insert into tb values('3')
insert into tb values('4')
insert into tb values('5')
declare @id varchar(8000)
set @id = ''
select @id = @id + id + ',' from tb
set @id = left(@id,len(@id) - 1)
print @id
-- 先生成一张临时表玩下 :)
if exists (select * from dbo.sysobjects where id = object_id(N'temtable') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table temtable
SELECT * INTO temtable
FROM(
SELECT '1' AS temcol
UNION ALL
SELECT '2' AS temcol
UNION ALL
SELECT '3' AS temcol
)T
--SQL 主体
SELECT temcol = STUFF(REPLACE(REPLACE(
(
SELECT temcol
FROM temtable AS N
FOR XML AUTO
), '<N temcol="', ','), '"/>', ''), 1, 1, '')
--输出系统表sysobjects的colid
select @output = coalesce(@output+',', '') + cast(colid as varchar) from syscolumns where id = object_id('sysobjects') order by colid
print @output
/*
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25
*/
--输出系统表sysobjects的列名
set @output=null
select @output = coalesce(@output+',', '') + name from syscolumns where id = object_id('sysobjects') order by colid
print @output
/*
name,id,xtype,uid,info,status,base_schema_ver,replinfo,parent_obj,crdate,ftcatid,schema_ver,stats_schema_ver,type,userstat,sysstat,indexdel,refdate,version,deltrig,instrig,updtrig,seltrig,category,cache
*/