How do I calculate the SIZE of stored procedures in SQL Server 2005?

Question

I was asked for a comprehensive breakdown on space used within a specific database. I know I can use sys.dm_db_partition_stats in SQL Server 2005 to figure out how much space each table in a database is using, but is there any way to determine the individual and total size of the stored procedures in a database? (Short of opening each one and counting the characters, of course.)

Total space used by stored procs is not likely to be significant (compared to actual data), but with hundreds of them, it could add up.

Answer 1

;WITH ROUTINES AS (
    -- CANNOT use INFORMATION_SCHEMA.ROUTINES because of 4000 character limit
    SELECT o.type_desc AS ROUTINE_TYPE
            ,o.[name] AS ROUTINE_NAME
            ,m.definition AS ROUTINE_DEFINITION
    FROM sys.sql_modules AS m
    INNER JOIN sys.objects AS o
        ON m.object_id = o.object_id
)
SELECT SUM(LEN(ROUTINE_DEFINITION))
FROM ROUTINES