Counting lines of code in all SPs in Database

Question

Post reply

Counting lines of code in all SPs in Database

Charlottecb

Hall of Fame

Points: 3932
More actions
April 27, 2005 at 3:41 am

#61129

Hi all.
Does anyone have a way to return the total number of lines of code in all stored procs in an SQLserver 2000 DB ?
Thanks.
CCB

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

Just curious, why do you want to count the 'lines'..?

If you have two procs, with statements like these - are they to be considered the same # lines or not?

create myProc1

as

select id, name, col1, col2 from myTable

go

create myProc2

as

select id,

name,

col1,

col2

from myTable

go

For the sake of storage, both are represented as a single row in syscomments. The parser doesn't pay any attention to whitespaces or CR/LF chars when parsing code. Makes it a bit difficult to define what a 'line' really is

/Kenneth

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

Also not sure why, but what about scripting all code into one file and use an ordinary text editor?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Charlottecb Hall of Fame Points: 3932 More actions · Answer 3

Perfect Frank, its exactly what I need.

Thanks again.

CCB

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 4

..still curious I am....

anyways, it should work I guess, after some editing of the file.. I suppose that you don't want to count each line of

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

(or similar) that also gets inserted into the scriptfile between each procedure..?

/Kenneth

noeld SSC Guru Points: 96590 More actions · Answer 5

If you use VSS for your back-end object scripts, that functionality is already there

* Noel

Rob Sanguin SSChasing Mays Points: 610 More actions · Answer 6

A simple QA approach - set QA to 'Results in text' mode and then run:-

SELECT SC.TEXT FROM SYSCOMMENTS SC INNER JOIN SYSOBJECTS SO ON SC.ID = SO.ID

WHERE SO.NAME LIKE 'proc%' OR SO.NAME like 'func%'

replace 'proc%' and 'func%' with whatever naming convention you use for SPs and UDFs or triggers.

If you move the cursor to the end of the results (text) pane it will show the number of lines - simple!

Peter E. Kierstead SSCarpal Tunnel Points: 4114 More actions · Answer 7

Create Procedure OneLine(@p1 int) As Select * from TBL where Col = @p1

How would you count this? As mentioned above, what is the definition of a "line"...

PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.

Roy Ashbrook SSChasing Mays Points: 632 More actions · Answer 8

sqldmo example. i got some kind of null error. probably pretty easy to fix, but i just tossed some error resume statements in there

strServer = inputbox("enter server name")

strDatabase = inputbox("enter database name")

storedprocs = 0

linecount = 0

with createobject("sqldmo.sqlserver")

.LoginSecure = TRUE

.Connect strServer

set colProcs = .Databases(strDatabase).StoredProcedures

for each objSToredProcedure in colProcs

on error resume next

with CreateObject("VBScript.RegExp")

.Global = True

.Pattern = vbcrlf

set colMatches = .Execute(objSToredProcedure.text)

if isobject(colmatches) then linecount = linecount + colMatches.Count

end with

on error goto 0

wscript.echo linecount & " lines, " & colProcs.count & " procs"

end with