Can I open or save storedprocedure's and function's body from MSMS (from TSQL not from UI) automatically?

Question

I have to change a column's length. I have found names of procedures and function where it is used. It is possible to open or to save it automatically. There are above 300 object. I'll have to analize it manually, but I want to open it first

Answer 1

Try sp_helptext 'func or proc name' - it will return you the code.

If you need several procs and functions at once, filtered by some condition in their code, use the following script:

DECLARE @name VARCHAR(100)
DECLARE @getNames CURSOR

SET @getNames = CURSOR FOR 
    SELECT o.name 
    FROM sysobjects o 
    WHERE 
        type = 'P' AND 
        o.name IN (
            SELECT ROUTINE_NAME 
            FROM INFORMATION_SCHEMA.ROUTINES 
            WHERE ROUTINE_DEFINITION LIKE '%your condition here%'
        )

OPEN @getNames
    FETCH NEXT
    FROM @getNames INTO @name
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC sp_helptext @name
        FETCH NEXT
        FROM @getNames INTO @name
    END
    CLOSE @getNames
    DEALLOCATE @getNames
GO

This will give you code for all the procs and funcitons - you can save this to file, or open in the new window, etc.

_{If in Management Studio, you may hit Ctrl+T before running the query - it will bring the results as plaintext. To get back to grid results, use Ctrl+D}