CREATE FUNCTION dbo.FnGetDefinition(@ObjId INT)
RETURNS @DefTable TABLE(ObjectDef VARCHAR(MAX))
AS
BEGIN
INSERT INTO @DefTable SELECT object_definition(@ObjID)
RETURN
END;
GO
SELECT P.name,D.ObjectDef  FROM sys.procedures As P
CROSS APPLY dbo.FnGetDefinition(p.object_id) As D

select 
o.name,
sm.definition
from
sys.objects o
inner join
sys.sql_modules sm
on
o.object_id = sm.object_id
where
o.type = 'p'

select 
  OBJECT_SCHEMA_NAME(object_id) AS SchemaName,
  OBJECT_NAME(object_id)        AS ObjectName,
  definition                    AS ObjectDefinition
FROM sys.sql_modules