SQL Server Using sp_HElpText to view stored procedure

oknwwptz  于 2023-10-15  发布在  其他
关注(0)|答案(1)|浏览(118)

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?

2wnc66cl

2wnc66cl1#

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

相关问题