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>
------------------------------------------------------------- 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.