Why should I care how many lines of code my database has?
You are right, it shouldn’t matter.
But when you have a lot of code is also the beefiest that brings problems.
So what do you suggests?
Pinal Dave already gave us a solution but I wanted to do the extra mile and give more details:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
SELECT DB_NAME(DB_ID()) AS [DB_Name], CASE WHEN TYPE = 'TR' THEN 'Trigger' WHEN TYPE = 'P' THEN 'Stored Procedure' WHEN TYPE = 'FN' THEN 'Scalar Function' WHEN TYPE = 'IF' THEN 'Inline Table-Valued Function' WHEN TYPE = 'TF' THEN 'Multi-Statement Table-Valued Function' WHEN TYPE = 'V' THEN 'View' ELSE 'Unknown' END AS [Object Type], COUNT(*) AS Object_Count, SUM(LinesOfCode) AS LinesOfCode FROM ( SELECT TYPE, LEN(definition) - LEN(REPLACE(definition, CHAR(10), '')) AS LinesOfCode, OBJECT_NAME(OBJECT_ID) AS NameOfObject FROM sys.all_sql_modules a JOIN sysobjects s ON a.OBJECT_ID = s.id WHERE OBJECTPROPERTY(OBJECT_ID, 'IsMSShipped') = 0 ) SubQuery GROUP BY TYPE ORDER BY LinesOfCode DESC SELECT DB_NAME(DB_ID()) AS [DB_Name], CASE WHEN TYPE = 'TR' THEN 'Trigger' WHEN TYPE = 'P' THEN 'Stored Procedure' WHEN TYPE = 'FN' THEN 'Scalar Function' WHEN TYPE = 'IF' THEN 'Inline Table-Valued Function' WHEN TYPE = 'TF' THEN 'Multi-Statement Table-Valued Function' WHEN TYPE = 'V' THEN 'View' ELSE 'Unknown' END AS [Object Type], NameOfObject AS [Object Name], LinesOfCode FROM ( SELECT TYPE, LEN(definition) - LEN(REPLACE(definition, CHAR(10), '')) AS LinesOfCode, OBJECT_NAME(OBJECT_ID) AS NameOfObject FROM sys.all_sql_modules a JOIN sysobjects s ON a.OBJECT_ID = s.id WHERE OBJECTPROPERTY(OBJECT_ID, 'IsMSShipped') = 0 ) SubQuery ORDER BY LinesOfCode DESC |
So now you have the list of object counts with the relative lines of code.
But also how many lines of code per object:
And that’s all folks.