Mareo Code

Microsoft SQL Server 2005

I almost finished my current C# project where I have created a lot of stored procedures. By a lot I mean almost 200 :) (I know it is a personal feeling :)) Few days ago I was just curious how many lines I wrote in my stored procedures. To satisfy my curiosity I wrote T-SQL code which show me the truth ;)

Blank lines with just Enter pressed are not counted.

Score for my project database is: 142 stored procedures, 9419 lines (most of all 537 lines in one SP)

@ResultType = 1

@ResultType = 0

So what is your best score? ;) How many SPs and lines did you write in your project? :)

SQL code below.

Usage: just run below code in context of your current database or use stored procedure

-- =============================================
-- Author:        Marek Sliwinski ( http://code.mareoblo.pl )
-- Version:        1.0
-- Create date: 2008-08-08
-- Description:    Query to show number of lines in all User stored
--                 procedures in current database
--         Parameters (@ResultType):
--          0 - show number of lines grouped by stored procedure name
--          1 - show total number of lines for all stored procedures
-- =============================================
SET NOCOUNT ON;

DECLARE
     @id            INT
    ,@sproc_name    NVARCHAR(512)
    ,@ResultType    TINYINT

DECLARE  @sproc_text      TABLE (textline NVARCHAR(MAX) NULL)
DECLARE  @sproc_container TABLE (textline NVARCHAR(MAX) NULL
, sproc_name NVARCHAR(512) NOT NULL)

-- ! SET PARAMETER !
-- 0 - show number of lines grouped by stored procedure name
-- 1 - show total number of lines for all stored procedures
SELECT
    @ResultType = 1

SELECT
    @id = min(object_id)
FROM
    sys.procedures
WHERE
    [type] = 'P'
    AND is_ms_shipped = 0
    AND [name] NOT LIKE 'sp[_]%diagram%'

WHILE @id IS NOT NULL
    BEGIN
        SELECT
            @sproc_name = [name]
        FROM
            sys.procedures
        WHERE
            [type] = 'P'
            AND is_ms_shipped = 0
            AND [name] NOT LIKE 'sp[_]%diagram%'
            AND object_id = @id

        INSERT INTO @sproc_text
            EXEC sp_helptext @sproc_name

        INSERT INTO @sproc_container
            SELECT
                *
                ,@sproc_name
            FROM
                @sproc_text

        DELETE FROM @sproc_text

        SELECT
            @id = min(object_id)
        FROM
            sys.procedures
        WHERE
            [type] = 'P'
            AND is_ms_shipped = 0
            AND [name] NOT LIKE 'sp[_]%diagram%'
            AND object_id > @id
    END

--remove empty lines, only \r\n
DELETE FROM
    @sproc_container
WHERE
    textline = char(13)+char(10)

IF (@ResultType = 1)
    SELECT
         COUNT(DISTINCT sproc_name) as NumberOfProcedures
        ,COUNT(*) as TotalLinesInAllProcedures
    FROM
        @sproc_container
ELSE IF (@ResultType = 0)
    SELECT
         sproc_name as ProcedureName
        ,COUNT(*) as LinesInProcedure
    FROM
        @sproc_container
    GROUP BY
        sproc_name
    ORDER BY
        LinesInProcedure DESC
        ,sproc_name ASC
ELSE
    PRINT 'Variable ResultType has to be in: 0,1'

And below same code but as stored procedure, usage:

exec [custom_ShowNumberOfLinesInSP]

exec [custom_ShowNumberOfLinesInSP] 1

exec [custom_ShowNumberOfLinesInSP] 0

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Marek Sliwinski ( http://code.mareoblo.pl )
-- Version:        1.0
-- Create date: 2008-08-08
-- Description:    Query to show number of lines in all User stored
--                 procedures in current database
--       Parameters (@ResultType):
--         0 - show number of lines grouped by stored procedure name
--         1 - show total number of lines for all stored procedures
-- =============================================
CREATE PROCEDURE [dbo].[custom_ShowNumberOfLinesInSP]
        @ResultType    TINYINT    = 1
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE
     @id            INT
    ,@sproc_name    NVARCHAR(512)

DECLARE  @sproc_text      TABLE (textline NVARCHAR(MAX) NULL)
DECLARE  @sproc_container TABLE (textline NVARCHAR(MAX) NULL
, sproc_name NVARCHAR(512) NOT NULL)

SELECT
    @id = min(object_id)
FROM
    sys.procedures
WHERE
    [type] = 'P'
    AND is_ms_shipped = 0
    AND [name] NOT LIKE 'sp[_]%diagram%'

WHILE @id IS NOT NULL
    BEGIN
        SELECT
            @sproc_name = [name]
        FROM
            sys.procedures
        WHERE
            [type] = 'P'
            AND is_ms_shipped = 0
            AND [name] NOT LIKE 'sp[_]%diagram%'
            AND object_id = @id

        INSERT INTO @sproc_text
            EXEC sp_helptext @sproc_name

        INSERT INTO @sproc_container
            SELECT
                *
                ,@sproc_name
            FROM
                @sproc_text

        DELETE FROM @sproc_text                

        SELECT
            @id = min(object_id)
        FROM
            sys.procedures
        WHERE
            [type] = 'P'
            AND is_ms_shipped = 0
            AND [name] NOT LIKE 'sp[_]%diagram%'
            AND object_id > @id
    END

--remove empty lines, only \r\n
DELETE FROM
    @sproc_container
WHERE
    textline = char(13)+char(10) 

IF (@ResultType = 1)
    SELECT
         COUNT(DISTINCT sproc_name) as NumberOfProcedures
        ,COUNT(*) as TotalLinesInAllProcedures
    FROM
        @sproc_container
ELSE IF (@ResultType = 0)
    SELECT
         sproc_name as ProcedureName
        ,COUNT(*) as LinesInProcedure
    FROM
        @sproc_container
    GROUP BY
        sproc_name
    ORDER BY
        LinesInProcedure DESC
        ,sproc_name ASC
ELSE
    PRINT 'Variable ResultType has to be in: 0,1'

    RETURN(0)
END