Search code examples
sqlsql-serverstored-proceduresdata-extraction

Extracting data from sp_helptext

I am currently pulling the description for 370 procedures using sp_helptext. What I am wondering if any one has a concept on how I would be able to pull the procedure description for each ap (which I have in an excel list) without having to manually go through them each?

Solution

  • If I understand your question correctly then this can be done using the system function OBJECT_DEFINITION as below:

    select 
    [name] as 'SP Name', 
    object_definition(object_id) as 'SP Definition'
from sys.procedures
where type = 'P'

    Will return all stored procedures name and source code.

    Can also be found in the sys.sql_modules catalog view for the definition column.

    select 
    [definition] as 'SP Definition'
from sys.sql_modules
where [definition] like '%procedure%'