Introduction

Why should everyone know these stored procedures? Let me explain by telling a story. Up until recently, I've done a lot of my SQL development work right inside Visual Studio. The Server Explorer did a good job of displaying the information I wanted and made it easy to create and modify tables and stored procedures. This was up until VS 2008, when the Server Explorer started to get a lot more finicky. The biggest problems seemed to happen if the database connection dropped for some reason (VPN disconnect, connection terminated by server, laptop went to sleep). In prior versions, attempting to use the connection after a disconnect would result in an error dialog, but once that error dialog was dismissed (easily done with the Enter key), re-attempting the same action would restore the connection and everything would be fine. Not so in VS 2008! Now you get an error dialog, and an empty tab. Merely re-attempting the same action without first closing the tab is futile, and closing the tab is not a simple press-Enter operation like the dialog was. As simple as this sounds, it made the difference between an acceptable environment and an intolerable one. It was time to find another way to interact with SQL Server.

What about SSMS Object explorer?

Well, simply put, it's not my cup of tea. At all. I don't like how many nodes I have to expand to get at the information I want, like the list of columns in a table. I don't like that all objects start with their owner names, making four extra keystrokes ("dbo.") I have to type before I even start searching for the object I want. Once I do find it, I can't just press Enter to open it. And so on.

T-SQL to the rescue!

It turns out that MSSQL comes with a lot of really nice and helpful built-in system stored procedures that lend themselves really well to working with SQL Server in an interactive query session.

Listing Procedures

• sp_tables: lists tables and views matching a pattern
• sp_stored_procedures: lists stored procedures and functions matching a pattern
• sp_helptrigger: lists a table's triggers

Usually, the first thing you need to do is to find the actual object you want information about. Both sp_tables and sp_stored_procedures can be called without parameters to list everything, or they can be called with a string parameter, and all objects whose name matches a LIKE comparison are returned. For example, sp_tables 'a%' will return all tables whose name starts with 'a'.

Info Procedures

• sp_help: lists different information about the object, depending on the object's type
• sp_helptext: lists the object's definition

Once you've narrowed down what object you want info on, these are usually the next stop. sp_help lists a lot of useful information when a table is passed, including column definitions, indexes, constraints, and referencing tables. When a stored procedure is passed, it returns a list of parameters the procedure accepts. sp_helptext returns the actual object definition, so if a stored procedure is passed, sp_helptext returns the text of the stored procedure, good for inspecting logic, or even copying and pasting into another tab for modification.

Custom Procedures

In the course of interacting with SQL Server this way, I've concocted a few stored procedures of my own to help make things easier. I've blogged about some of them before, but I think they bear repeating in this context.

print_long

create procedure dbo.print_long
  @string nvarchar(max)
AS
SET NOCOUNT ON

set @string = rtrim( @string )

declare @cr char(1), @lf char(1)
set @cr = char(13)
set @lf = char(10)

declare @len int, @cr_index int, @lf_index int, @crlf_index int, @has_cr_and_lf bit, @left nvarchar(4000), @reverse nvarchar(4000)
set @len = 4000

while ( len( @string ) > @len )
begin
  set @left = left( @string, @len )
  set @reverse = reverse( @left )
  set @cr_index = @len - charindex( @cr, @reverse ) + 1
  set @lf_index = @len - charindex( @lf, @reverse ) + 1
  set @crlf_index = case when @cr_index < @lf_index then @cr_index else @lf_index end
  set @has_cr_and_lf = case when @cr_index < @len and @lf_index < @len then 1 else 0 end
  print left( @string, @crlf_index - 1 )
  set @string = right( @string, len( @string ) - @crlf_index - @has_cr_and_lf )
end

print @string

This first one is merely a prerequisite for the next procedure. It works around the 8000-byte limitation of the built-in print statement by breaking @string into multiple 4000-character chunks. For reasons that are now lost to time, the built-in function DATALENGTH() did not work as desired, so LEN() was used instead.

print_def

create procedure print_def
  @name sysname
as
set nocount on

declare @def nvarchar(max)
select @def = sm.definition
from sys.sql_modules sm
join sys.objects o on sm.object_id = o.object_id
where o.name = @name

exec print_long @def

This custom stored procedure addresses an annoyance I have with sp_helptext, which is that sp_helptext returns its results as data, which means that unless you set SSMS to return data as text, you get the stored procedure back in a grid. To work around this, print_def outputs the definition of the object in the Messages pane instead.

find_def

create procedure find_def
  @def varchar(max)
as
set nocount on

select distinct o.name
from sys.sql_modules sm
join sys.objects o on o.object_id = sm.object_id
where sm.definition like '%' + @def + '%'
order by o.name

return @@error

This custom stored procedure returns the names of objects whose definitions contain the passed text. I personally opted to write it with implicit LIKE wildcards surrounding it, because that was how I tended to use it most, but if you prefer, you could modify this procedure so it works more like sp_tables, only performing wildcard searches where explicitly specified. This is really useful for finding which stored procedures reference a certain table, or another stored procedure.

Conclusion

With these built-in stored procedures, and a few custom ones to make things easier, I have a complete replacement for Visual Studio 2008's finicky Server Explorer. If I ever have an interrupted connection in SSMS, all I have to do is press F5 again; there are no modal error dialogs to clear or tabs to close. I have found this new environment easy to work with, and I hope that you will too.