如何从临时表中检索字段名称 (SQL Server 2008)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/756080/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to retrieve field names from temporary table (SQL Server 2008)
提问by Anthony
I'm using SQL Server 2008. Say I create a temporary table like this one:
我正在使用 SQL Server 2008。假设我创建了一个这样的临时表：
create table #MyTempTable (col1 int,col2 varchar(10))
How can I retrieve the list of fields dynamically? I would like to see something like this:
如何动态检索字段列表？我想看到这样的事情：
Fields:
col1
col2
I was thinking of querying sys.columns but it doesn't seem to store any info about temporary tables. Any ideas?
我正在考虑查询 sys.columns 但它似乎没有存储有关临时表的任何信息。有任何想法吗？
回答by kristof
select * from tempdb.sys.columns where object_id =
object_id('tempdb..#mytemptable');
回答by Ed Guiness
select *
from tempdb.INFORMATION_SCHEMA.COLUMNS
where table_name like '#MyTempTable%'
回答by quillbreaker
To use information_schema and not collide with other sessions:
要使用 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'))
回答by marc_s
The temporary tables are defined in "tempdb", and the table names are "mangled".
临时表在“tempdb”中定义，表名被“破坏”。
This query should do the trick:
这个查询应该可以解决问题：
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
马克
回答by Nishad
you can do it by following way too ..
你也可以通过以下方式做到..
create table #test (a int, b char(1))
select * From #test
exec tempdb..sp_columns '#test'
回答by Franklin
Anthony
安东尼
try the below one. it will give ur expected output
试试下面的。它会给你预期的输出
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%'
回答by elle0087
select *
from tempdb.INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME=OBJECT_NAME(OBJECT_ID('#table'))
