April 27, 2005 at 3:41 am
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
April 27, 2005 at 5:07 am
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
April 27, 2005 at 5:49 am
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]
April 27, 2005 at 5:57 am
Perfect Frank, its exactly what I need.
Thanks again.
CCB
April 27, 2005 at 6:08 am
..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
April 27, 2005 at 8:15 am
If you use VSS for your back-end object scripts, that functionality is already there
* Noel
April 28, 2005 at 3:44 am
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!
April 28, 2005 at 9:41 am
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"...
April 28, 2005 at 4:02 pm
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
next
.Disconnect
wscript.echo linecount & " lines, " & colProcs.count & " procs"
end with
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply