如何从临时表中检索字段名(SQL Server 2008)

Question

我正在使用SQL Server 2008。 假设我创建了一个临时表，如下所示：

create table #MyTempTable (col1 int,col2 varchar(10))

我如何动态检索字段列表？我想看到类似于这样的内容：

Fields:
col1
col2

我在考虑查询sys.columns，但它似乎没有存储任何关于临时表的信息。有什么想法吗？

- Anthony

Answer 1

select * from tempdb.sys.columns where object_id =
object_id('tempdb..#mytemptable');

- kristof

---

很好的答案。使用 select name from tempdb.sys.tables 获取所有临时表的名称。 - Raaghav

确认此方法不会返回通过其他连接创建的 #temp 表中的数据。而使用 LIKE #temp% 的替代方法则可以返回这些数据。 - Morvael

Answer 2

select * 
from tempdb.INFORMATION_SCHEMA.COLUMNS
where table_name like '#MyTempTable%'

- Ed Guiness

---

这个答案更好。表名和列的数据类型都包括在内。如果你的where语句包含了一些临时表的列表，那么这个答案会更出色，因为你可以区分哪些列来自于哪个临时表。 - VISQL

@VISQL 实际上，被接受的答案更好，因为它不使用“INFORMATION_SCHEMA”。您可以通过连接到“sys.types”轻松获取数据类型，并可以通过“OBJECT_NAME（object_id，database_id）”或连接到“tempdb.sys.tables”轻松获取表名。这就是“INFORMATION_SCHEMA”视图所做的所有工作。 - Solomon Rutzky

问题在于，如果您使用两个名称相似的临时表（例如#TEMP1和#TEMP12），那么当将#TEMP1用作表名时，您还会获得#TEMP12的列。 - apc

使用“#MyTempTable[][][_]％”可以更好地确保您不会获得以您正在查找的表的名称开头的其他表。 - apc

被点踩了：验证了被接受的答案无法从通过不同连接创建的#temp表中返回数据，而这种方法可以。被接受的答案更安全。 - Morvael

Answer 3

使用information_schema而不与其他会话发生冲突：

select * 
from tempdb.INFORMATION_SCHEMA.COLUMNS
where table_name =
    object_name(
        object_id('tempdb..#test'),
        (select database_id from sys.databases where name = 'tempdb'))

- quillbreaker

Answer 4

临时表定义在 "tempdb" 中，表名是 "mangled"。

这个查询应该能解决问题：

select c.*
from tempdb.sys.columns c
inner join tempdb.sys.tables t ON c.object_id = t.object_id
where t.name like '#MyTempTable%'

马克

- marc_s

---

我认为这可能会跨越范围。如果它是一次性代码，那没问题。但如果它是有真正寿命的代码，就有问题了。 - jcollum

是的，这是一个糟糕的解决方案 - 如果多个连接创建了相同名称的临时表（例如在从应用程序调用的过程中），那么这将是错误的。 - Tao

被踩为：已验证被接受的答案无法返回通过不同连接创建的 #temp 表中的数据，而这种方法可以。被接受的答案更安全。 - Morvael

Answer 5

你也可以通过以下方式完成。。。

create table #test (a int, b char(1))

select * From #test

exec tempdb..sp_columns '#test'

- Nishad

---

提供了编写显式“CREATE TABLE”的所有必要内容： exec tempdb..sp_columns '#test' - yzorg

Answer 6

安东尼

请尝试下面的代码，它将会给出您期望的输出结果。

select c.name as Fields from 
tempdb.sys.columns c
    inner join tempdb.sys.tables t
 ON c.object_id = t.object_id
where t.name like '#MyTempTable%'

- Franklin

Answer 7

select * 
from tempdb.INFORMATION_SCHEMA.COLUMNS 
where TABLE_NAME=OBJECT_NAME(OBJECT_ID('#table'))

- elle0087