I frequently need to look up object definitions when I’m developing or query tuning. You could use Object Explorer in SSMS, but that takes a lot of time and clicking. Then there’s the Alt+F1 shortcut, which will trigger the sp_help stored procedure. That however, comes with a lot of annoying built-in limitations, so a few years ago I started building and maintaining a “better Alt+F1” of sorts.

I decided to call it “Ctrl+3“. But I suppose you could assign it to any keyboard shortcut you want.

Alt+F1, sp_help

There are a number of pre-configured query shortcuts in Management Studio and SQL Server. When you select an object and press Alt+F1, SSMS will execute the system stored procedure sp_help and pass the name of that object as a parameter. That will return the details of the object in a number of tables. Here’s a typical output:

This nifty little hack has served me for many, many years, starting somewhere way back on SQL Server 2000 if I recall correctly. We’ve come a long way since, but the tool really hasn’t changed. Just some of the annoying limitations I’ve encountered include:

• You can’t reference objects in other databases. If you try selecting a three-part, fully qualified object name, you’ll get:
  “The database name component of the object qualifier must be the name of the current database.”
• You can’t see included columns in indexes, only the actual index columns.
• You can’t tell how many rows are in a table (or in a filtered index) or the size of an object.
• There’s no information if a table or (indexed) view is partitioned and how.
• No dependency information.
• And while we’re on that subject, wouldn’t it be awesome to have a dependency graph!?

sp_ctrl3

I designed my sp_ctrl3 procedure from the ground up to be a development tool that I can install on any dev environment that I use regularly. I wanted a detailed overview of a specific database object as well as copy-paste-friendly T-SQL code. For instance,

• No “length” column on an int column
• Proper scale/precision values on datatypes
• “NULL” or “NOT NULL” instead of “yes” or “no”
• Identity column syntax
• “nvarchar(50)” instead of “nvarchar”, “100”
• Column defaults
• Complete index definitions
• GRANT/DENY permission statements
• The object_id of the object in sys.objects

… and so on. Here’s a screenshot of the same table as before, displayed with sp_ctrl3:

See how you could select all rows and columns from the second table, paste it into a new window and pretty much just add “CREATE TABLE xyz (” at the beginning – and you’re done.

How to install

• Go to the Downloads page and download the .sql script.
• Execute the script in a database of choice on a dev server. This will create the sp_ctrl3 stored procedure.
• If you execute the script in the master database, it’ll also register the stored procedure using a undocumented, unsupported system procedure called “sys.sp_MS_marksystemobject”. This allows you to run sp_ctrl3 in any database on the server without prefixing it with “master.dbo.”
• This last step won’t work on Azure SQL Database – in fact, on Azure SQL Database, you’ll have to install the script in each database where you need it, as you can’t access the master database.

Registering the SSMS shortcut

From here on, it’s one more simple step to get you up and running. In SQL Server Management Studio, go to “Tools”, “Options” menu item, then expand “Environment”, “Keyboard” and “Query shortcuts”.

Some of these shortcuts are fixed and cannot be edited, but feel free to use any which one you like. Simply type “sp_ctrl3” without quotes or spaces in a suitable slot and close the Options dialog.

You’ll have to open a new query window for the new shortcut to take effect.

EDIT: As of November 2018, this script is on GitHub, so you can fork your own copy, make improvements to it, and send me a pull request!

The usual disclaimer

I use this script everywhere I go and I’ve tweaked and patched it for several years now. However, that doesn’t make it perfect, and your setup may look different than mine. So please don’t put it in your production environment.

And do let me know if you find bugs or missing features. Let’s get this trending!