Count your lines of code

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


sys.sql_modules AS sm JOIN

sys.objects AS o ON sm.object_id = o.object_id

where o.is_ms_shipped =0 and 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

Leave a Reply

Your email address will not be published. Required fields are marked *