How to: exec sp_help on temp tables?

Say you have a temp table and you want to see the columns names.

For example, I was trying to convert a query from using a #temp table to a CTE instead, and wanted to see the column list and resulting data types of the #temp table.

Sp_help is a helpful SQL Server system sproc to return schema of objects. It's that magic that happens when you press Alt+F1 in SSMS. (Side note: showing someone the Alt+F1 shortcut in SSMS for the first time and seeing their life change for the better is really rewarding.)

But Alt+F1 doesn't work on #temp tables because it tries to execute this:

exec sp_help #temp;

Which fails with the error:

Msg 15009, Level 16, State 1, Procedure sp_help, Line 79 [Batch Start Line 21]
The object '#temp' does not exist in database 'myuserdb' or is invalid for this operation.

That's because as far as sys.objects is concerned, temp tables don't exist in the user database you're working in, but always in the TempDB.

So you change your database context to TempDB:

use tempdb;
exec sp_help #temp;

And it works! You get back the expected sp_help output.

But what if you don't want to change your database context, or you are working in Azure SQL Database, where you youths can't use USE? Use this, instead:

exec tempdb.sys.sp_help #temp;

You can also shorten this to:

    exec tempdb..sp_help #temp;

Why? The ".." syntax or "dot dot" syntax shortens the schema by assuming the default schema. The default schema is users is dbo. This still works for sp_help and other system objects are in the sys schema but are addressable through the dbo schema.

Helpfully, both of these work:

    exec tempdb.sys.sp_help #temp;
    exec tempdb.dbo.sp_help #temp;

As for Alt+F1, resign yourself to not using it for temp tables. You could map another shortcut key in SSMS or Azure Data Studio to exec tempdb..sp_help instead of exec sp_help if you wanted to.

How to: exec sp_help on temp tables?

Rate

Share

Share

Rate