Estimating stored procedure size

sysprocedures stores the normalized query tree for procedures. Including other overheads, this size allows for 2 rows per 2K page. To estimate the size of a single stored procedure, view, or trigger, use:

select count(*) as "approximate size in KB"
from sysprocedures
where id = object_id("procedure_name")

For example, to find the size of the titleid_proc in pubs2:

select count(*) 
from sysprocedures
where id = object_id("titleid_proc")

approximate size in KB
---------------------- 
                     3

If the plan is in cache, the monCachedProcedures monitoring table includes its size.