/*
<documentation>
  <summary>Count size in bytes veiws, triggers, procedures and function in database.</summary>
  <returns>1 data set: RoutinType, SUM LENGTH of objects, SUM DATALENGTH.</returns>
  <issues>No</issues>
  <author>Cade Roux</author>
  <created>2008-10-20</created>
  <modified>2019-06-26 by Konstantin Taranov</modified>
  <version>1.0</version>
  <sourceLink>https://github.com/ktaranov/sqlserver-kit/blob/master/Scripts/Objects_Size_In_Database.sql</sourceLink>
  <originalLink>https://stackoverflow.com/a/219740/2298061</originalLink>
</documentation>
*/

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

WITH CTE_Routine AS (
    /* Can not use INFORMATION_SCHEMA.ROUTINES because of 4000 character limit */
    SELECT o.type_desc     AS RoutineType
         , o.[name]        AS RoutineName
         , m.[definition]  AS RoutineDefinition
    FROM   sys.sql_modules AS m 
    INNER JOIN sys.objects AS o ON m.object_id = o.object_id
)
SELECT RoutineType
     , SUM(LEN(RoutineDefinition))            AS RoutineLen
     /* DATALENGTH for counting trailing space in the end of objects definitions */
     , SUM(DATALENGTH(RoutineDefinition)) / 2 AS RoutineDatalength
FROM   CTE_Routine
GROUP BY RoutineType;