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

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

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).