Finding #temp table in sysobjects / INFORMATION_SCHEMA

Question

I am running a `SELECT INTO` statement like this so I can manipulate the data before finally dropping the table. ``` SELECT colA, colB, colC INTO #preop FROM tblRANDOM ``` However when I run the statement and then, without dropping the newly created table, I then run either of the following statements, the table isn't found? Even scanning through object explorer I can't see it. Where should I be looking? ``` SELECT [name] FROM sysobjects WHERE [name] = N'#preop' SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '#preop' ```

Accepted Answer

Temp tables aren't stored in the local database, they're stored in `tempdb`. Also their name isn't what you named them; it has a hex code suffix and a bunch of underscores to disambiguate between sessions. And you should use `sys.objects` or `sys.tables`, not the deprecated `sysobjects` (note the big warning at the top) or the incomplete and stale `INFORMATION_SCHEMA` views.

The following query will show that if you have multiple sessions with the same #temp table name (`#preop`), they show up in the metadata with distinct names, and the name is not just `#preop`. To be crystal clear, this is not how to find the `object_id` for this specific #temp table in your session, it will also return others. Again, to avoid any confusion, I never intended anyone to expect this is a safe query for finding a #temp table in your session named `#preop`. It is merely demonstrating why there isn't a table named `#preop` in `tempdb.sys.objects`:

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

If you are trying to determine if such an object exists in your session, so that you know if you should drop it first, you should do:

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

In modern versions (SQL Server 2016+), this is even easier:

```
DROP TABLE IF EXISTS #preop;
```

However if this code is in a stored procedure then there really isn't any need to do that... the table should be dropped automatically when the stored procedure goes out of scope.

Finding #temp table in sysobjects / INFORMATION_SCHEMA

Solution

Problem