查看从存储过程创建的临时表

时间：2020-12-03 03:35:34

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.

6 个解决方案

#1

14  

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 #table or ##table

有几种临时表,我认为你可以使用SP使用后没有删除的表。只是确保您没有两次调用相同的SP,否则您将尝试创建现有表时出错。或者只是在看到它的内容后删除临时表。因此,不要使用表变量(@table),而只需使用#table或## table

From 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).

    • 本地临时表前缀为单个数字符号(#)作为其名称的第一个字符,如(#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.

    • 本地临时表仅在当前会话中可见,或者您可以说它们仅对用户的当前连接可见。当用户断开与Microsoft SQL Server实例的连接时,将删除它们。

Global temporary tables

  • Global temporary tables prefix with double number sign (##) as the first character of their names, like (##table_name).

    • 全局临时表前缀带有双号(##)作为其名称的第一个字符,如(## 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.

    • 当引用该表的所有用户与Microsoft SQL Server断开连接时,将删除它们。

#2

7  

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.

之后您可以将其更改回来。如果您无法使用原始程序,请将其复制并编辑副本。

#3

5  

I built a few stored procedures which allow you to query the content of a temp table created in another session.

我构建了一些存储过程,允许您查询在另一个会话中创建的临时表的内容。

See sp_select project 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'来显示表的内容。

#4

3  

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表。

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表。

#5

1  

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

#6

1  

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

这给出了所有临时表的详细信息

#1

14  

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 #table or ##table

有几种临时表,我认为你可以使用SP使用后没有删除的表。只是确保您没有两次调用相同的SP,否则您将尝试创建现有表时出错。或者只是在看到它的内容后删除临时表。因此,不要使用表变量(@table),而只需使用#table或## table

From 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).

    • 本地临时表前缀为单个数字符号(#)作为其名称的第一个字符,如(#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.

    • 本地临时表仅在当前会话中可见,或者您可以说它们仅对用户的当前连接可见。当用户断开与Microsoft SQL Server实例的连接时,将删除它们。

Global temporary tables

  • Global temporary tables prefix with double number sign (##) as the first character of their names, like (##table_name).

    • 全局临时表前缀带有双号(##)作为其名称的第一个字符,如(## 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.

    • 当引用该表的所有用户与Microsoft SQL Server断开连接时,将删除它们。

#2

7  

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.

之后您可以将其更改回来。如果您无法使用原始程序,请将其复制并编辑副本。

#3

5  

I built a few stored procedures which allow you to query the content of a temp table created in another session.

我构建了一些存储过程,允许您查询在另一个会话中创建的临时表的内容。

See sp_select project 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'来显示表的内容。

#4

3  

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表。

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表。

#5

1  

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

#6

1  

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

这给出了所有临时表的详细信息

标签：

相关文章