Everyone has a script, a hack or a checklist they can’t function without. In this edition of T-SQL Tuesday, Bert Wagner challenged us to write about our favorite scripts. This is my take.

sp_ctrl3

My absolute number one favorite homebrew tool is without a doubt sp_ctrl3. I started building it a long time ago to replace the built-in sp_help procedure in SQL Server, which is accessible using the Alt+F1 shortcut in Management Studio.

sp_help shows you object information on database objects, such as column definitions, parameters, indexes, etc, but it’s old (I remember it in SQL Server 2000, but it’s probably way older than that) and it hasn’t really aged that well since then. What’s more, sp_help won’t show you the more technical details and features from newer versions (like filters and included columns on indexes) , so you can’t really just copy and paste information from it very reliably or effortlessly.

Like the name implies, cp_ctrl3 aims to address some of those issues, and years later, I find myself adding features to it whenever there’s something I miss.

How do I set it up?

Install the procedure (available from the Downloads page) on your SQL Server, and set it up as a keyboard shortcut.

That’s it.

You can choose to install it in the “master” database, that way it’ll be accessible in all databases (the installation script registers it as a system object, using sys.sp_MS_marksystemobject), or you could install it in one or more specific databases.

On Azure SQL Database, you’ll need to put it in the user database as you can’t install anything in “master”.

How does it work?

Select the name of an object in the text editor and press the assigned keyboard shortcut, and SSMS will run the stored procedure “sp_ctrl” with the selected text as the argument. The output will vary depending on what type of object you selected, and what properties/features it uses.

Here’s an example of a table:

The top recordset contains the schema and name of the object. You can also see the filegroup and any partition schema it sits on, as well as any data compression options, the row count and the space it takes up in the database.

The second recordset contains columns and parameters. The output is formatted in such a way that you can copy and paste it into a CREATE TABLE statement without having to make a lot of changes to it. It’ll even throw in a trailing comma for you.

The next two recordsets show you all the indexes and unique/primary key constraints as well as any foreign key constraints on the table. As with the columns, it’ll give you a copy-paste friendly way to recreate indexes and constraints with a minimum of effort. Note that the foreign key constraints include both referenced and referencing tables.

For extra geek nirvana, you get an ASCII art dependency graph, so you can see what object references and foreign keys relate to your object. The numbers in parentheses are row counts. Cross-database references are still some ways down on my to-do list.

And finally, at the bottom, you get the storage information – how much space the object and all of its clustered/non-clustered indexes take up on the various partitions and file groups.

More selling points

• View information on temp tables.
• View information on objects in other databases, using three-part naming.
• Columns like nvarchar(100) shows up like nvarchar(100), not nvarchar with 200 length.
• Output column names are quoted (with [ ] brackets) when they are reserved keywords or contain any non-standard characters.

Bonus: database search

Just select any plaintext you like and press the keyboard shortcut, and the procedure will look through tables, columns and code for that string (including wildcard searches).

Want it?

Go on and download it. It’s free. But mind you, it’s a best-effort piece of software, so don’t put it in your production server if you’re the least unsure. It does come with more overhead than sp_help, but you may find that the practicality of it makes it worth it.

And more!

If you like this, you’ll probably appreciate my SSMS Level-Up presentation that I also presented on Groupby.org. It includes sp_ctrl3, but also a ton of other work hacks in Management Studio!