Like me, you’ve probably hummed along happily enough just using SQL Server as a place to store data. You’ve no doubt noticed, but never really dug into the all the folders that appear when you create a database.
In this post, I’’m going to briefly discuss two really useful items that are tucked away in the “System Stored Procedures” folder.
First up is sp_helptext. This stored procedure
displays the definition of a user-defined rule, default, unencrypted Transact-SQL stored procedure, user-defined Transact-SQL function, trigger, computed column, CHECK constraint, view, or system object such as a system stored procedure.
Stripping away the Charlie Brown Adult Trombone Talk, what this basically means is that it will display the SQL that was used to create stored procs, functions, views and several other objects. For example:
Combined with what we learned last time, we can press Ctrl+T, and get a format better suited for editing:
From here, we are just a few clicks away from being able to start editing, or at least looking at this with some syntax highlighting.
That’s certainly quicker than mousing through the sql navigator, scrolling around until you find what you want and then right-clicking and picking an option.
You can pay me via Pay-Pal for the millions of dollars I just saved you.
Next up is sp_helptext’s awesome sibling sp_help. According to the documentation, this stored procedure
Reports information about a database object (any object listed in the sys.sysobjects compatibility view), a user-defined data type, or a data type.
Which, to be honest, kind of does a pretty bad job of selling itself. But let’s take a look at it in action against a stored procedure
Again, this is a great shortcut over navigating your way to this with the mouse.
Running this against a table provides an even wider variety of valuable information.
These are just two of literally hundreds of stored procedures that your database gets for free in SQL Server and combined over the past few months have probably saved me literally hours of less useful navigation techniques.
* I folded a black tshirt up and used it as a mask once.