Home > Link, SQL > BISQL # 90 – How to find Number of lines of Code in procedure or function

BISQL # 90 – How to find Number of lines of Code in procedure or function

Hi friends ,

One day i remember in very recent days that , thought writing post on same :

Boss : You guys have Done Nice job !!

We : Oh really thanks you (Smile Hot smileOur bosses are really great sometime they really share comment , thoughts everything )

Boss : Hmm , this project Is really huge …

We :  Ya (More happy .. Yes our hardwork have shown us result ..Fingers crossed)

Boss : By the ways how many lines of code you have written .

We : Don't tell anyone smileDon't tell anyone smileDon't tell anyone smileDon't tell anyone smileDon't tell anyone smileDon't tell anyone smileDon't tell anyone smileDon't tell anyone smileDon't tell anyone smileDon't tell anyone smileDon't tell anyone smileDon't tell anyone smileDon't tell anyone smileDon't tell anyone smileDon't tell anyone smileDon't tell anyone smileDon't tell anyone smile

Boss : Ohh !! its ok ..

That’s it , that time i decide we must have some queries to find out that ?

Though i know this question is irrelevant and independent of any kind of development , Because we can write whole program in one line .

imageimageimage

And here is evolution of script ………….

Following script gives all information of Procedure and function in which we have how many number of lines of code .

In today article i am just providing number of lines per objects in upcoming post i will give exact number of lines of code also on one script itself.
We will use our favourite DB Adventure Works DB for this script

--Total No of line of Code per Object 
--No of line of code in each object 
SELECT t.sp_name AS 'Stored Procedure', 
       SUM(t.lines_of_code) - 1 AS 'No of Lines in code ', 
       t.type_desc AS 'Object Description'
FROM
(
SELECT    o.name AS sp_name, 
       (LEN(c.text) - LEN(REPLACE(c.text, CHAR(10), ''))) AS lines_of_code,
        CASE WHEN o.xtype = 'P' THEN 'Stored Procedure'
            WHEN o.xtype IN ('FN', 'IF', 'TF') THEN 'Function'
            END AS type_desc
FROM        sysobjects o
INNER JOIN    syscomments c
ON            c.id = o.id
WHERE        o.xtype IN ('P', 'FN', 'IF', 'TF')
            AND o.category = 0
            AND o.name 
            NOT IN ('fn_diagramobjects', 'sp_alterdiagram', 
                    'sp_creatediagram', 'sp_dropdiagram', 
                    'sp_helpdiagramdefinition', 'sp_helpdiagrams', 
                    'sp_renamediagram', 'sp_upgraddiagrams',
                     'sysdiagrams')
) t
GROUP BY t.sp_name, t.type_desc
ORDER BY 1

Lets see output of same query

image

As we can easily count number of lines of code per object .

I know its not related to programming as such because we never think of line of code and all ..

I know its not complete number line (What That ?? Sick smile)

Hope you will like this post on finding Number of lines of Code in procedure or function.

If you really like reading my blog and understood at lest few thing then please don’t forget to subscribe my blog .

If you wan daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog

Where todays links are

Link Resource Website

You may also find following interesting post :

For More information related to BI World visit my Mentalist Blog

Link Resource Blog >> Daily Interesting links

SQL Server Mentalist >> SQL Learning Blog

Business Intelligence Mentalist >> BI World

Connect With me on

| Facebook |Twitter | LinkedIn| Google+ | WordPress | RSS |

Copyright © 2011 – 2012 Vishal Pawar

Advertisements
Categories: Link, SQL
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: