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.

Virtual User Group Presentations, Feb 23-24

I'll be presenting at two virtual SQL data platform communities this week on a couple well-rehearsed topics, each with fresh updates.

Tuesday, February 23, 2021

12:00 PM to 1:30 PM EST

DBA Fundamentals Virtual User Group

Event link: SQL Admin Best Practices with DMVs | Slidedeck | Toolbox

Wednesday, February 24, 2021
1:00 PM to 2:00 PM EST
Database Professionals Virtual Meetup Group
Event link: Security Principals 101 | Slidedeck and Samples

Looking forward to speaking to both these audiences this week, interacting and answering questions! It will be my first time speaking to a public user group community since joining Microsoft in November and I'm eager to get rolling again. 

PASS Virtual Summit 2020: I'm (Virtually) Presenting

I'll be presenting at 7AM Central Time in the first timeslot of the main three-day PASS Virtual Summit 2020 conference. It's long been a goal of mine to present at the best of all international SQL conferences, and this is the first year it happened for me, so I'm thrilled to be a part of it.

It's not too late to register for the all-online event, with the same great quality content as always, at a fraction of the usual cost of going to Seattle.

Like many (but not all) presentations at PASS Virtual Summit, my 75-minute presentation will feature roughly 60 minutes of pre-recorded (and painstakingly edited) content, with the rest of the time available for live Q&A with the speaker. 

My presentation will cover a lot of important foundational material about security, accounts, authentication. 

• For folks new to SQL Server security design and administration, this will be a great foundation for your learning. 
• For those experienced in SQL admin, this will be a thorough evaluation of what you know, or thought you know, and maybe some gaps in what you know. 
• I think there is content in here to interest everyone in the SQL career lifecycle, and I’m not just guessing at that. I got my first DBA job in 2006. I’ve been giving a presentation on Security at User Groups and SQLSaturdays basics for years, it was one of the first topics I started speaking technically on a decade ago. As my own experience has deepened and broadened throughout my career, so has the content I build into this presentation. 

So I’m going to start basic, and build quickly from there, focusing my content around common hurdles and tasks that database administrators face, in the hopes of deepening or broadening your experience, as well. 

I'm setting the stage for a good conversation around security at PASS Virtual Summit 2020, especially around how permissions behave inside each database, how you can design database security, the relationships between logins, users, and databases. My session one of a four part Learning Pathway on security. We worked together over the past four months to make sure we're presenting a thorough conversation on security. 

In subsequent presentations over the next three days:

• John Morehouse is presenting on Understanding Modern Data Encryption Offerings for SQL Server, including a lot more information on all the various sorts of encryption, plus some important security features of SQL Server like Transparent Data Encryption and Always Encrypted.
• Jeff Renz is presenting on Securing Your Data In Azure: Tips and Tricks, which covers Advanced Threat Detection, the Azure Key Vault, cloud secure connection strings, and certified data sets for security in PowerBI.
• Ed Leighton-Dick will cap it off with a presentation on Building a Security Dashboard for SQL Server, talking about when certs expire and what does that actually mean, more on Azure Advanced Threat Detection, SQL Audit and monitoring.