Show all stored procedure definitions (content) for editing

Question

Apologies if this has been asked before, the suggested 'related' questions weren't relevant and my own search turned up very little.

I need to list the definitions (code) of all my stored procedures, so that I can start working through all 450 of them and adding in semi-colons to work toward v2014 standards.

I found the following code here and adapted it slightly:

SELECT
    obj.Name AS SPName,
    REPLACE(modu.definition, 'CREATE PROC', 'ALTER PROC') + 'GO' AS SPDefinition
FROM
    sys.sql_modules modu INNER JOIN
    sys.objects obj ON modu.object_id = obj.object_id
WHERE
    (obj.type = 'P')
ORDER BY
    obj.name;

The code above is run using the SA account.

I assumed that this provided exactly what I needed, but, I've just noticed that somebody on MSDN is claiming it doesn't show the full text as expected.

When pasting the results into the SSMS editor, I have 46,000 lines of code to work through, so its not apparent to me if anything is missing. I'd like to be sure I've started this process correctly before wasting a day in the editor.

Therefore, I am reaching out to you pro's to ask if this is a genuine shortcoming of this approach, or is there an alternative method rather than one-by-one?

Answer 1

The problem mentioned on MSDN has nothing to do with sys.sql_modules or OBJECT_DEFINITION(); they've misinterpreted the problem. What actually happened is they were thwarted by an output limitation in Management Studio, which by default will only show 255 characters and at most 8192 in any output tuple in Results to Text.

So first, make sure you change this to 8192:

Tools > Options > Query Results > SQL Server > Results to Text >
Maximum number of characters displayed in each column

Now you can make one script for all the procedures that are less than 4K:

SELECT REPLACE(m.definition, N'CREATE PROC', N'ALTER PROC') 
  + CHAR(13) + CHAR(10) + N'GO'
FROM sys.sql_modules AS m
INNER JOIN sys.procedures AS p
ON m.[object_id] = p.[object_id]
WHERE LEN(m.definition) <= 4000
ORDER BY p.name;

(Of course, your replacement is naïve - what if they have create procedure (more than one space) or you are on a case sensitive collation? You may have to fix some of those manually.)

Now, you only have to worry about manually deriving the script for the larger procedures. First, see if you have any:

SELECT name 
  FROM sys.procedures 
  WHERE LEN(OBJECT_DEFINITION([object_id])) > 4000; 

I talk a little about some workarounds here (in the context of dynamic SQL, but the same would be true if you assigned OBJECT_DEFINITION() to a variable).

Mr. Magoo is right though, you will get the full definition without having to worry about truncation if you use the scripting functionality within Management Studio:

• right-click a procedure, modify or script as > alter to >
• in Object Explorer Details, do the same as above but for multiple
• right-click a database, tasks > generate scripts > (but this does CREATE, not ALTER, so you'll need to perform replacements still)

As an aside, while you are adding semi-colons everywhere, I also suggest ensuring that all of your procedures are created with schema prefixes (e.g. dbo.procedurename, not just procedurename), and also for all object references inside the procedure. Those are likely to cause problems long before a lack of semi-colons ever will. Also a few general things to keep in mind while performing your changes.

Answer 2

I would use the Object Explorer in SQL Server Management Studio and go from the top, one procedure at a time.

1. Right click on the procedure.
2. Select Modify.
3. Edit the procedure code.
4. List item
5. Press F5 to execute the modification of the procedure.
6. Close the tab.
7. Go to 1.

Makes it easy to divide the work between you and your friend that does the same going from the bottom of the list of procedures. And it will not leave you with a half done gigantic wall of code when you need to go on a break or something.

You also minimize the time span where you can end up with editing conflicts between what you do and what someone else did with the same procedure. If you after 4 days of editing the 46,000 lines of code eventually execute the thing you could overwrite something someone else has done during the time of your editing. If you use source control (and you should) you would "only" end up with having to merge the changes.

Answer 3

In this scenario system table has always been useful.

Please use this query.

Select * From SYS.Comments Where Text = 'Stored Procedure Name'

Although this table as multiple rows for a single object_id but putting a concat function on the table helps to get a single ROW for a single object_id.