Search code examples
sql-serverstored-procedures

Using sp_HElpText to view stored procedure

How can I view a stored procedure by using sp_HelpText?

I am not sure about its exact spelling? I am looking for something like below,

EXEC sp_HelpText WHERE storedprocedurename Like '%spInsert%'

Is it possible way to view the stored procedure?

Solution

  • Create below stored procedure in your database. It will helpful to you in future also.

    EXEC Searchinall 'TextSpName'    

CREATE PROCEDURE [dbo].[Searchinall] 
    (@strFind AS VARCHAR(MAX))
    AS
    BEGIN
        SET NOCOUNT ON; 
        --to find string in all procedures        
        BEGIN
            SELECT OBJECT_NAME(OBJECT_ID) SP_Name,
                   OBJECT_DEFINITION(OBJECT_ID) SP_Definition
            FROM   sys.procedures
            WHERE  OBJECT_DEFINITION(OBJECT_ID) LIKE '%' + @strFind + '%'
        END
    END

    It will provide all matched store procedure list, which contains/using given keyword. TextSpName