SQL query to get Stored Procedure crop result if the routine_definition is greater than 4000 char

Question

I use this query to get all the stored procedure in my database (I'm using Microsoft SQL Server 2008):

 SELECT
     SPECIFIC_NAME, ROUTINE_DEFINITION
 FROM 
     INFORMATION_SCHEMA.ROUTINES

For almost all result everything is ok, but for the row with a very long ROUTINE_DEFINITION the result is cropped.

Do you know how to solve it?

Answer 1

Please try with sp_helptext 'ProcedureName' or you can use either sys.sql_modules or OBJECT_DEFINITION([object_id]) function to get the stored procedure text. All this gives your exact code. Information_Schema.Routines will give up to max of nvarchar(4000).

Answer 2

If you are using SQL Server Management Studio then it is possible that it does not display all of the text of the stored procedure (since I believe there is a 8192 character limit for each column).

One thing you can do to check this is to verify the length of the string stored in the column:

SELECT
     SPECIFIC_NAME
   , ROUTINE_DEFINITION
   , LEN(ROUTINE_DEFINITION) [Length in characters]
FROM INFORMATION_SCHEMA.ROUTINES

If this length is greater than 8192 then it is possible that the truncation occurs only on display level (SSMS) and not in the actual code of the stored procedure.

There is however a way to increase this number, as mentioned here - Options (Query Results/SQL Server/Results to Grid Page)

To change the options for the current queries, click Query Options on the Query menu, or right-click in the SQL Server Query window and select Query Options.

...

Maximum Characters Retrieved

Non XML data: Enter a number from 1 through 65535 to specify the maximum number of characters that will be displayed in each cell.