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;
exec tempdb..sp_help #temp;exec tempdb.sys.sp_help #temp; exec tempdb.dbo.sp_help #temp;
