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.
a useful code, as a start. however this idea goes beyond just lines of code.
i turned it into automated sql version control system.
https://github.com/maximnl/msql/blob/main/MSQL_DATABASE_CODE.sql
Description: Retrieves all user-defined database objects (stored
procedures, functions, views, triggers) with their
code definitions and line counts, and inserts them
into a tracking table. Provides version control for
database projects by automatically tracking code changes
over time. Allows rollback to any previous version by
retrieving historical definitions. Useful for code
analysis, documentation, change tracking, and understanding
database complexity.
This code can be scheduled in daily data jobs (SQL Agent,
SSIS, cron, etc.) to keep SQL versions automatically
without manual intervention.