I Don’t Want To Use the Object Browser (sp_helptext)

Have you ever needed the definition of a view, stored procedure, or user defined function?  I sure hope you have – otherwise reading any more of this posting will be more than pointless.

For those of you still with me, have you ever also not wanted to find that definition without have to browse through the object browser?  With 100 or a 1,000 objects this can be a pain and sometimes the mouse clicks just aren’t worth the effort.  Well, they’re are worth the effort but why take your hands off the keyboard to grab the mouse.

Two paragraphs and hopefully some are still with me… so the solution is sp_helptext.  This has been around since sliced bread and continues to be as useful as sliced bread.
Just execute the procedure against the desired object (it helps if the results will be text):

EXEC sp_helptext 'HumanResources.uspUpdateEmployeeHireInfo'</blockquote>

And viola!

-------------------------------------------------------------
CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeHireInfo]
@BusinessEntityID [int],
@JobTitle [nvarchar](50),
@HireDate [datetime],
@RateChangeDate [datetime],
@Rate [money],
@PayFrequency [tinyint],
@CurrentFlag [dbo].[Flag]
WITH EXECUTE AS CALLER
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION;
UPDATE [HumanResources].[Employee]
SET [JobTitle] = @JobTitle
,[HireDate] = @HireDate
,[CurrentFlag] = @CurrentFlag
WHERE [BusinessEntityID] = @BusinessEntityID;
INSERT INTO [HumanResources].[EmployeePayHistory]
([BusinessEntityID]
,[RateChangeDate]
,[Rate]
,[PayFrequency])
VALUES (@BusinessEntityID, @RateChangeDate, @Rate, @PayFrequency);
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- Rollback any active or uncommittable transactions before
-- inserting information in the ErrorLog
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END
EXECUTE [dbo].[uspLogError];
END CATCH;
END;

Pretty great!  Unfortunately this doesn’t provide any output for tables.

EXEC sp_helptext 'Person.Address'

Will provide you the following error

Msg 15197, Level 16, State 1, Procedure sp_helptext, Line 107
There is no text for object 'Person.Address'.

Hopefully this helps a few of you conserve some calories and time by avoiding a grab at the mouse in the future.