How are stored procedures stored in SQL?

A lot of our application logic lives in stored procedures. How can I obtain
the number of lines of code in all of those stored procedures? Can this be
queried?
While we are at it, how about the total number of fields in all of the user
tables?The code is stored in the system table "syscomments".
Example:
use northwind
go
create table #t (colA int identity, colB nvarchar(4000))
insert into #t (colB)
execute sp_helptext 'dbo.sales by year'
select * from #t
order by colA
drop table #t
go
AMB
"JM" wrote:

> A lot of our application logic lives in stored procedures. How can I obta
in
> the number of lines of code in all of those stored procedures? Can this b
e
> queried?
> While we are at it, how about the total number of fields in all of the use
r
> tables?
>
>|||1. I'm guessing you want the total amount of lines for all SPs.
Script out all SPs into Notepad. Don't forget to turn off Word Wrap.
2. Check if this works:
SELECT COUNT(dbo.sysobjects.name) AS totalColumns
FROM dbo.sysobjects INNER JOIN
dbo.syscolumns ON dbo.sysobjects.id =
dbo.syscolumns.id
WHERE (dbo.sysobjects.xtype = 'u')
"JM" <JM@.nospam.com> wrote in message
news:edM6WVeFFHA.3492@.TK2MSFTNGP12.phx.gbl...
> A lot of our application logic lives in stored procedures. How can I
obtain
> the number of lines of code in all of those stored procedures? Can this
be
> queried?
> While we are at it, how about the total number of fields in all of the
user
> tables?
>|||Thanks to both. Very helpful! We have 300,000 lines of code in sp's, I
actually expected the number to be about double that. We have ~24,000
columns in user tables.
This is going in the overall documentation of the app.
"raydan" <rdanjou@.savantsoftNOSPAM.com> wrote in message
news:%23PEPBkeFFHA.560@.TK2MSFTNGP15.phx.gbl...
> 1. I'm guessing you want the total amount of lines for all SPs.
> Script out all SPs into Notepad. Don't forget to turn off Word Wrap.
> 2. Check if this works:
> SELECT COUNT(dbo.sysobjects.name) AS totalColumns
> FROM dbo.sysobjects INNER JOIN
> dbo.syscolumns ON dbo.sysobjects.id =
> dbo.syscolumns.id
> WHERE (dbo.sysobjects.xtype = 'u')
> "JM" <JM@.nospam.com> wrote in message
> news:edM6WVeFFHA.3492@.TK2MSFTNGP12.phx.gbl...
> obtain
> be
> user
>|||This is an adaptation of Alejandro's solution:
Gives you a better count then my first suggestion.
I know. It's a (shudder) cursor solution.
use northwind
go
set nocount on
create table #t (colA int identity, colB nvarchar(4000))
declare @.sp varchar(50)
DECLARE count_Cursor CURSOR FOR
SELECT name
FROM sysobjects
WHERE xtype = 'p'
OPEN count_Cursor
FETCH NEXT FROM count_Cursor INTO @.sp
WHILE @.@.FETCH_STATUS = 0
begin
insert into #t (colB)
execute sp_helptext @.sp
FETCH NEXT FROM count_Cursor INTO @.sp
end
select count(*) from #t
drop table #t
CLOSE count_Cursor
DEALLOCATE count_Cursor
"raydan" <rdanjou@.savantsoftNOSPAM.com> wrote in message
news:%23PEPBkeFFHA.560@.TK2MSFTNGP15.phx.gbl...
> 1. I'm guessing you want the total amount of lines for all SPs.
> Script out all SPs into Notepad. Don't forget to turn off Word Wrap.
> 2. Check if this works:
> SELECT COUNT(dbo.sysobjects.name) AS totalColumns
> FROM dbo.sysobjects INNER JOIN
> dbo.syscolumns ON dbo.sysobjects.id =
> dbo.syscolumns.id
> WHERE (dbo.sysobjects.xtype = 'u')
> "JM" <JM@.nospam.com> wrote in message
> news:edM6WVeFFHA.3492@.TK2MSFTNGP12.phx.gbl...
> obtain
> be
> user
>