How much code has gotten into production? How many different objects have this code? Isn’t that also a measure of just how ‘big’ your responsibility is? If you have a massive application with thousands of stored procs and functions…guess who put them there? Guess who has to navigate thru them to find just the one that needs to be corrected? That’s right, you…the DBA. So I decided it would be nice to know how many lines of code had ‘slipped in’ to my production machines. Below is the code to do that on a single machine:
select DB_NAME(db_id()) ,SUM(lines_of_code), Type_desc from ( SELECT OBJECT_NAME(sm.object_id) AS object_name, o.type_desc, case when
(len(sm.definition) – len(replace(sm.definition, char(10), ”))) < –each line of code defined by linereturn
LEN(sm.definition)/40 –Assuming 60 char per line of code
then LEN(sm.definition)/40
else (len(sm.definition) – len(replace(sm.definition, char(10), ”)))
end as lines_of_code
FROM
sys.sql_modules AS sm JOIN
sys.objects AS o ON sm.object_id = o.object_id
where o.is_ms_shipped =0 and o.name not in (‘fn_diagramobjects’, ‘sp_alterdiagram’, ‘sp_creatediagram’, ‘sp_dropdiagram’, ‘sp_helpdiagramdefinition’, ‘sp_helpdiagrams’, ‘sp_renamediagram’, ‘sp_upgraddiagrams’, ‘sysdiagrams’)
–ORDER BY o.type, object_name;
) tst group by type_desc