从Sql脚本中查找全局临时表名称 [英] Find the Global Temp Table Name from the Sql Script

问题描述

我有一些存储过程。多次创建一些全局表（以##开头）。我想列出表名。例如 -



1-

I have some Stored Procedure. Where multiple times some global tables are created (start with ##). I want to list out the table Names. For example -

1-

create table ##customer
(
Customerid bigint
)

2-

2-

if(OBJECT_ID('tempdb..##Department')) is null
begin
create  table    ##Department
(
Departmentid bigint
)
end

3-

SELECT * FROM ## customer



我想列出Temp Table名称，这些名称是创建的，并且用作选择查询。请帮忙。使用Sql Query的第一优先级。如果你在sql中没有想法，那么使用C＃。我将存储过程内容传递给C＃方法。

3-
SELECT * FROM ##customer

I want to list out the Temp Table names, which are created and which are used as a select query. Please help. 1st priority using Sql Query. If you do not have idea in sql, then using C# . I will pass the Stored procedure content to C# method.

推荐答案

如果您有权访问...从这里开始：

Provided you have access... Start here:

SELECT name
, definition
FROM sys.objects A
INNER JOIN sys.sql_modules B
ON A.object_id=B.object_id
WHERE type='p'
AND definition LIKE '%##%'

它应该为您提供使用全局临时表的过程的名称和定义，这样可以缩小范围。

It should get you the names and definitions of the procedures that use a global temp table, that'll narrow things down a bit.

您可以像这样查询临时表：

You can query the temp tables like this:

SELECT * FROM tempdb.sys.tables
WHERE name LIKE '##%'

但如果我理解你的话，你也想看看它们的用法。好吧，由于没有引用，你实际上无法在任何时候告诉所有语句使用一个特定的表。但是，如果你真的需要，你可以追踪它。这仍然不容易 - 请参考此应用程序的代码： https://expressprofiler.codeplex.com/SourceControl/latest [ ^ ]

谢谢朋友。我在c＃中使用正则表达式完成了这个，并使用CLR在sql中调用了这些方法。

Thanks friends. I have done this using Regular expression in c#, and called those methods in sql by using CLR.

这篇关于从Sql脚本中查找全局临时表名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！