SQL 查看从存储过程创建的临时表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/126012/
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
View Temporary Table Created from Stored Procedure
提问by Vinod
I have a stored procedure in SQL 2005. The Stored Procedure is actually creating temporary tables in the beginning of SP and deleting it in the end. I am now debugging the SP in VS 2005. In between the SP i would want to know the contents into the temporary table. Can anybody help in in viewing the contents of the temporary table at run time.
我在 SQL 2005 中有一个存储过程。存储过程实际上是在 SP 的开头创建临时表并在最后删除它。我现在正在 VS 2005 中调试 SP。在 SP 之间，我想知道临时表中的内容。任何人都可以帮助在运行时查看临时表的内容。
Thanks Vinod T
感谢 Vinod T
回答by Ilya Kochetov
There are several kinds of temporary tables, I think you could use the table which is not dropped after SP used it. Just make sure you don't call the same SP twice or you'll get an error trying to create an existing table. Or just drop the temp table after you see it's content. So instead of using a table variable (@table) just use #tableor ##table
临时表有几种，我想你可以使用SP使用后没有删除的表。只要确保不要两次调用同一个 SP，否则尝试创建现有表时会出错。或者在看到它的内容后删除临时表。因此，不要使用表变量 ( @table)，只需使用#table或##table
From http://arplis.com/temporary-tables-in-microsoft-sql-server/:
从http://arplis.com/temporary-tables-in-microsoft-sql-server/：
Local Temporary Tables
本地临时表
- Local temporary tables prefix with single number sign (#) as the first character of their names, like (#table_name).
- Local temporary tables are visible only in the current session OR you can say that they are visible only to the current connection for the user. They are deleted when the user disconnects from instances of Microsoft SQL Server.
- 本地临时表以单号 (#) 作为其名称的第一个字符作为前缀，例如 (#table_name)。
- 本地临时表仅在当前会话中可见，或者您可以说它们仅对用户的当前连接可见。当用户与 Microsoft SQL Server 实例断开连接时，它们将被删除。
Global temporary tables
全局临时表
- Global temporary tables prefix with double number sign (##) as the first character of their names, like (##table_name).
- Global temporary tables are visible to all sessions OR you can say that they are visible to any user after they are created.
- They are deleted when all users referencing the table disconnect from Microsoft SQL Server.
- 全局临时表以双数字符号 (##) 作为其名称的第一个字符作为前缀，例如 (##table_name)。
- 全局临时表对所有会话可见，或者您可以说它们在创建后对任何用户可见。
- 当所有引用该表的用户与 Microsoft SQL Server 断开连接时，它们将被删除。
回答by Galwegian
Edit the stored procedure to temporarily select * from the temp tables (possibly into another table or file, or just to the output pane) as it runs..?
编辑存储过程以在它运行时临时从临时表中选择 *（可能到另一个表或文件，或者只是到输出窗格）。
You can then change it back afterwards. If you can't mess with the original procedure, copy it and edit the copy.
然后，您可以稍后将其更改回来。如果您不能弄乱原始程序，请复制它并编辑副本。
回答by Filip De Vos
I built a few stored procedures which allow you to query the content of a temp table created in another session.
我构建了一些存储过程，它们允许您查询在另一个会话中创建的临时表的内容。
See sp_selectproject on github.
请参阅github 上的sp_select项目。
The content of the table can be displayed by running exec sp_select 'tempdb..#temp'from no matter which session.
exec sp_select 'tempdb..#temp'无论从哪个会话运行，都可以显示表格的内容。
回答by Gil
Bottom line: the default Visual Studio Microsoft debugger is not in the same session as the SQL code being executed and debugged.
底线：默认的 Visual Studio Microsoft 调试器与正在执行和调试的 SQL 代码不在同一个会话中。
So you can ONLY look at #temp tables by switching them to global ##temp tables or permanent tables or whatever technique you like best that works across sessions.
因此，您只能通过将#temp 表切换到全局##temp 表或永久表或您最喜欢的跨会话工作的任何技术来查看#temp 表。
note: this is VERY different from normal language debuggers... and I suspect kept that way by Microsoft on purpose... I've seen third party SQL debugger tools decades ago that didn't have this problem.
注意：这与普通语言调试器非常不同......我怀疑微软故意保持这种方式......几十年前我见过第三方 SQL 调试器工具没有这个问题。
There is no good technical reason why the debugger cannot be in the same session as your SQL code, thus allowing you to examine all produced contructs including #temp tables.
调试器不能与您的 SQL 代码在同一个会话中没有很好的技术原因，从而允许您检查所有生成的结构，包括 #temp 表。
回答by Vinod
This helped me.
这对我有帮助。
SELECT * FROM #Name
USE [TEMPDB]
GO
SELECT * FROM syscolumns
WHERE id = ( SELECT id FROM sysobjects WHERE [Name] LIKE '#Name%')
this gives the details of all the temp table
这给出了所有临时表的详细信息
回答by Luke Bennett
To expand on previous suggestions that you drop the data into a permanent table, you could try the following:
要扩展之前将数据放入永久表的建议，您可以尝试以下操作：
-- Get rid of the table if it already exists
if object_id('TempData') is not null
drop table TempData
select * into TempData from #TempTable
