CREATE TABLE tmp(a INT,b int)
go
DECLARE @sql VARCHAR(10)SET @sql = ''SELECT @sql = @sql + ',' + CONVERT(VARCHAR(8000),c.name)
FROM syscolumns c
WHERE c.id = OBJECT_ID('tmp')
ORDER BY colid
SELECT @sql
/*
---------
,b(1 row(s) affected)*/
SET @sql = ''SELECT @sql = @sql + ',' + CONVERT(VARCHAR(8000),c.name)
FROM syscolumns c
WHERE c.id = OBJECT_ID('tmp')
--ORDER BY colid
SELECT @sql
/*
----------
,a,b(1 row(s) affected)
*/
GO
DROP TABLE tmp
go
DECLARE @sql VARCHAR(10)SET @sql = ''SELECT @sql = @sql + ',' + CONVERT(VARCHAR(8000),c.name)
FROM syscolumns c
WHERE c.id = OBJECT_ID('tmp')
ORDER BY colid
SELECT @sql
/*
---------
,b(1 row(s) affected)*/
SET @sql = ''SELECT @sql = @sql + ',' + CONVERT(VARCHAR(8000),c.name)
FROM syscolumns c
WHERE c.id = OBJECT_ID('tmp')
--ORDER BY colid
SELECT @sql
/*
----------
,a,b(1 row(s) affected)
*/
GO
DROP TABLE tmp
select top 100 percent...
CREATE TABLE tmp(a INT,b int)
go
DECLARE @sql VARCHAR(10)SET @sql = ''SELECT TOP 100 percent @sql = @sql + ',' + CONVERT(VARCHAR(8000),c.name)
FROM syscolumns c
WHERE c.id = OBJECT_ID('tmp')
ORDER BY colid
SELECT @sql
/*
---------
,b(1 row(s) affected)*/
SET @sql = ''SELECT TOP 10000 @sql = @sql + ',' + CONVERT(VARCHAR(8000),c.name)
FROM syscolumns c
WHERE c.id = OBJECT_ID('tmp')
ORDER BY colid
SELECT @sql
/*
----------
,a,b(1 row(s) affected)
*/
GO
DROP TABLE tmp
----------
,a,b(1 row(s) affected)
----------
,a,b(1 row(s) affected)
http://blog.csdn.net/zjcxc/archive/2006/09/17/1233068.aspx
CREATE TABLE tmp(a INT,b int)
go
DECLARE @sql VARCHAR(10)SET @sql = ''SELECT @sql = @sql + ',' + CONVERT(VARCHAR(8000),c.name)
FROM syscolumns c
WHERE c.id = OBJECT_ID('tmp')
ORDER BY colid
SELECT @sql
/*
---------
,a,b(1 row(s) affected)*/
SET @sql = ''SELECT @sql = @sql + ',' + CONVERT(VARCHAR(8000),c.name)
FROM syscolumns c
WHERE c.id = OBJECT_ID('tmp')
--ORDER BY colid
SELECT @sql
/*
----------
,a,b(1 row(s) affected)
*/
GO
DROP TABLE tmp我这里的运行结果怎么和LZ的不一样?我这里得到的结果是一样的
FROM syscolumns c
WHERE c.id = OBJECT_ID('tmp')
ORDER BY colid
那么无论是否有order by,返回都是正确的。
但是使用 @sql = @sql + ',' + CONVERT(VARCHAR(8000),c.name) 就有问题了。不过我觉得上面这种写法也不是很好。完全依赖于SQL内部怎么处理...
;
查询分隔符
'
字符数据字符串分隔符
--
注释分隔符
/* ... */
注释分隔符。服务器不对 /* 和 */ 之间的注释进行处理。
Xp_
目录扩展存储过程的名称的开头,如 xp_cmdshell。