在sysobjects/INFORMATION_SCHEMA中查找#temp表

Question

我正在运行像这样的SELECT INTO语句，以便在最终删除表之前可以操作数据。

SELECT colA, colB, colC INTO #preop FROM tblRANDOM

然而，当我运行了这条语句后，并且没有删除新创建的表，然后我再运行以下任一语句时，无法找到该表？即使通过对象资源管理器进行扫描，我也找不到它。我应该在哪里查找？

SELECT [name] FROM sysobjects WHERE [name] = N'#preop'
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '#preop'

- stonypaul

Answer 1

临时表不存储在本地数据库中，它们存储在tempdb中。另外，它们的名称不是您所命名的名称；它有一个十六进制代码后缀和一堆下划线以区分会话之间的歧义。而且，你应该使用 sys.objects 或 sys.tables ，而不是已弃用的 sysobjects (请注意顶部的警告)或不完整和过时的 INFORMATION_SCHEMA 视图。

以下查询将显示，如果您有多个具有相同 #temp 表名称（#preop）的会话，则它们将以不同的名称显示在元数据中，并且名称不仅仅是 #preop。为了非常清楚，这不是查找您会话中特定 #temp 表的 object_id 的方法，它还会返回其他表。再次强调，为了避免任何混淆，我从未打算让任何人期望这是一个安全的查询，用于查找名为 #preop 的 #temp 表。它只是演示了为什么在 tempdb.sys.objects 中没有名为 #preop 的表：

SELECT name FROM tempdb.sys.objects WHERE name LIKE N'#preop[_]%';

如果您想确定这样的对象是否存在于您的会话中，以便您知道是否应该首先删除它，则应执行以下操作：

IF OBJECT_ID('tempdb.dbo.#preop') IS NOT NULL
BEGIN
  DROP TABLE #preop;
END

在现代版本中（SQL Server 2016+），这变得更加容易：

DROP TABLE IF EXISTS #preop;

然而，如果此代码位于存储过程中，则没有必要这样做...当存储过程超出范围时，表应该会自动删除。

- Aaron Bertrand

---

Aaron Bertrand - 谢谢 - 这很有用 - stonypaul

Aaron Bertrand-所以这应该在存储过程的开头工作，但由于权限错误而无法工作？IF EXISTS (SELECT name FROM tempdb.sys.objects WHERE name LIKE N'#preop[_]%') BEGIN DROP TABLE #preop END错误信息为：Msg 3701, Level 11, State 5, Line 3 Cannot drop the table '#preop', because it does not exist or you do not have permission. 抱歉，这看起来很糟糕，但是如何编写注释？ - stonypaul

@Paul 你不需要这样做。你需要更新问题并包含你尝试解决的所有问题。当前的问题是如何查找一个对象，而不是尝试删除它。 - Aaron Bertrand

@PaulStevens 你可以在注释中使用 \ 反引号 `，但是最好不要这样做。最好将其保留给零散的 标识符 或其他内容，而不是整个代码片段。 - Mathieu Guindon

使用 OBJECT_ID 对我来说似乎比使用 LIKE 更安全，因为它可以解决同时使用相同临时表名称的会话问题，不是吗？ - Charlieface

@Charlieface 这篇文章已经有将近10年的历史了，但第一个查询仅展示了如何识别使用该命名模式创建的表名（您的会话和可能还有其他人的）。我从未建议这是一种安全的方法来仅识别您自己的表。 - Aaron Bertrand

Answer 2

我希望以这种方式查询tempdb：

IF  EXISTS (SELECT * FROM tempdb.sys.objects 
             WHERE object_id = OBJECT_ID(N'tempdb.[dbo].[#MyProcedure]')
             AND type in (N'P', N'PC'))
BEGIN 
    print 'dropping [dbo].[#MyProcedure]'
    DROP PROCEDURE [dbo].[#MyProcedure]
END
GO

- Raf

---

使用OBJECT_ID函数和友好名称比使用LIKE谓词更具体。对我来说缺失的部分是在OBJECT_ID函数中[dbo]之前的[tempdb]数据库。 - gregsonian

@gregsonian 我在我的回答中演示的 LIKE 谓词并不是为了展示如何找到_您的 #temp 表的副本_。它是为了说明为什么 tempdb.sys.objects 中没有名为 #preop 的表。 - Aaron Bertrand

Answer 3

以下是我获取临时表列的方法：

CREATE TABLE #T (PK INT IDENTITY(1,1), APP_KEY INT PRIMARY KEY)

SELECT * FROM tempdb.INFORMATION_SCHEMA.COLUMNS c WHERE c.TABLE_NAME LIKE '#T%'

- Lysoll