/*
Count size in bytes veiws, triggers, procedures and function in database.
1 data set: RoutinType, SUM LENGTH of objects, SUM DATALENGTH.
No
Cade Roux
2008-10-20
2019-06-26 by Konstantin Taranov
1.0
https://github.com/ktaranov/sqlserver-kit/blob/master/Scripts/Objects_Size_In_Database.sql
https://stackoverflow.com/a/219740/2298061
*/
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;