How to get only stored procedures body in SQL Server

Question

While working on a database documentation topic, I encountered a situation and got stuck. Thanks in advance for potential help. Here are the facts:

I am trying to obtain only the body of certain stored procedures in my database.
Anything else, such as SP parameters or options - I don't need.
Googled around and all I've found is ways to obtain the entire SP text - most of them already known.
I've put together a solution as you can see below but it's not covering all the cases and it's not pretty.

Having defined this test SP:

CREATE PROCEDURE dbo.returnDay
    @addTheseDays SMALLINT = 0
AS
    -- This is just a test SP that retrieves 
    -- the current date if @addTheseDays isn't defined, 
    -- otherwise the current day + @addTheseDays 
    SELECT GETDATE() + @addTheseDays;
GO

What didn't help:

-- This doesn't help since it retrieves all SP text (including parameters and options part)
EXEC sp_helptext 'dbo.returnDay';

-- The ROUTINE_DEFINITION column also holds the entire SP text.
SELECT * 
FROM INFORMATION_SCHEMA.ROUTINES;

Workaround I've done and works, with exceptions:

DECLARE @spText VARCHAR(MAX)
SELECT @spText = object_definition(object_id('dbo.returnDay'))

SELECT SUBSTRING(@spText, CHARINDEX('AS', @spText, 0) + 2 , LEN(@spText)) AS spBody

This "ugly" string manipulation workaround works but only when the SP does not have "WITH EXECUTE AS CALLER" option or the parameters don't have "AS" as part of their name. In these cases then I get extra, unneeded info regarding the SP (again, only need SP body - only what is between the AS and batch terminator).

Also tried to use the first BEGIN and last END in the SP body (and get what's between) but since these are not mandatory in SQL Server and some SPs don't have them then I can't rely on them.

Any ideas and/or suggestions on how can I get only the SP body (code and comments) in a better way?

Indeed there is no other way . . . maybe one thing to try with your hack is 'AS' + CHAR(13) + CHAR(10) or whatever newline is used — ludwigmace, Oct 28 '15 at 15:16

score 1 · Answer 1 · edited May 23 '17 at 10:27

1

You will need parse the T-SQL sproc to obtain just the body. Have a look at RegEx to parse stored procedure and object names from DDL in a .sql file C#. There are several CLR assemblies that give you regex access within SQL, or perform in your application language if not purely SQL for your application.

edited May 23 '17 at 10:27

Community

1
1

answered Oct 28 '15 at 15:24

Andrew Loree

263
1
8

score 1 · Accepted Answer · answered Oct 28 '15 at 15:28

As a quick and dirty one-off you can look for AS as a single word, taking into account the single EXECUTE AS exception.

This will fail for any comments containing -AS- of course.

SELECT SUBSTRING(
    @spText, 
    PATINDEX('%[ ' + CHAR(13) + CHAR(10) + CHAR(9) + ']AS[ ' + CHAR(13) + CHAR(10) + CHAR(9) + ']%', REPLACE(@spText, 'WITH EXECUTE AS CALLER', 'WITH EXECUTE ?? CALLER')) + 3, 
    LEN(@spText))

How to get only stored procedures body in SQL Server

2 Answers2