blocks|key|1581038|text|临时表在"tempdb“中定义，表名为"mangled”。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1581039|下面的查询应该可以做到这一点：|1581040|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%25'|code-block|syntax|javascript|1581041|Marc|1581042|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

The temporary tables are defined in "tempdb", and the table names are "mangled". 

This query should do the trick:

<pre><code>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%'
</code></pre>

Marc

blocks|key|1365958|text|select+*+from+tempdb.sys.columns+where+object_id+=
object_id('tempdb..#mytemptable');|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1365959|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>select * from tempdb.sys.columns where object_id =
object_id('tempdb..#mytemptable');
</code></pre>

blocks|key|1581081|text|select+*+
from+tempdb.INFORMATION_SCHEMA.COLUMNS
where+table_name+like+'#MyTempTable%25'|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1581082|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>select * 
from tempdb.INFORMATION_SCHEMA.COLUMNS
where table_name like '#MyTempTable%'
</code></pre>

blocks|key|1366100|text|安东尼|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1366101|试试下面这个。它会给出你期望的输出|1366102|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%25'|code-block|syntax|javascript|1366103|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Anthony 

try the below one. it will give ur expected output

<pre><code>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%'
</code></pre>

blocks|key|1366162|text|你也可以按照下面的方法来做..|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1366163|create+table+#test+(a+int,+b+char(1))

select+*+From+#test

exec+tempdb..sp_columns+'#test'|code-block|syntax|javascript|1366164|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

you can do it by following way too ..

<pre><code>create table #test (a int, b char(1))

select * From #test

exec tempdb..sp_columns '#test'
</code></pre>

blocks|key|1366207|text|要使用information_schema且不与其他会话冲突，请执行以下操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1366208|select+*+
from+tempdb.INFORMATION_SCHEMA.COLUMNS
where+table_name+=
++++object_name(
++++++++object_id('tempdb..#test'),
++++++++(select+database_id+from+sys.databases+where+name+=+'tempdb'))|code-block|syntax|javascript|1366209|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

To use information_schema and not collide with other sessions:

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

blocks|key|1581350|text|select+*+
from+tempdb.INFORMATION_SCHEMA.COLUMNS+
where+TABLE_NAME=OBJECT_NAME(OBJECT_ID('#table'))|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1581351|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>select * 
from tempdb.INFORMATION_SCHEMA.COLUMNS 
where TABLE_NAME=OBJECT_NAME(OBJECT_ID('#table'))
</code></pre>

I'm using SQL Server 2008. Say I create a temporary table like this one:

<pre><code>create table #MyTempTable (col1 int,col2 varchar(10))
</code></pre>

How can I retrieve the list of fields dynamically? I would like to see something like this:

<pre><code>Fields:
col1
col2
</code></pre>

I was thinking of querying sys.columns but it doesn't seem to store any info about temporary tables. Any ideas?

How to retrieve field names from temporary table (SQL Server 2008)

我使用的是SQL Server 2008。假设我创建了一个临时表，如下所示：create table #MyTempTable (col1 int,col2 varchar(10))如何动态检索字段列表？我希望看到这样的情况：Fields:col1col2我正在考虑查询sys.columns，但它似乎没有存储任何有关临...

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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