Show all stored procedure definitions (content) for editing

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.

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.