SQL Server – How to Show All Stored Procedure Definitions for Editing

sql serversql-server-2012stored-procedurest-sql

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?

Best Answer

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.

Related Question