0

Hi i am using SQL Server 2008 R2, i have lots of procedures in the database. amongest those i want to find the biggest procedure in terms of rows ? is it possible to find the procedure having the count as their number of rows in it. please can somebody help me to get this.

Thanks! in adavanced.

Vikrant More
  • 5,182
  • 23
  • 58
  • 90
  • 1
    The number of rows emitted by a procedure might vary dependant upon current data and parameters passed in. You could set up a trace capturing the procedure calls and the `rows` column. Not sure how useful that would be though. Why do you want to do this? – Martin Smith Jun 15 '12 at 06:59

3 Answers3

3

Using this substring count solution to count the line breaks (nchar(10)):

select o.type, o.name, 
    len(m.definition) - len(replace(m.definition, nchar(10), '')) as rows,
    m.* 
from sys.sql_modules m
inner join sys.objects o on m.object_id = o.object_id
--order by type, name
order by 3 desc
Community
  • 1
  • 1
devio
  • 36,858
  • 7
  • 80
  • 143
1

Not sure I understand your question. Are you looking for something like:

select 
    routine_name, 
    DATALENGTH(ROUTINE_DEFINITION) 
from INFORMATION_SCHEMA.ROUTINES 
order by 2 desc
David Brabant
  • 41,623
  • 16
  • 83
  • 111
1

In terms of character count instead of rowcount you can try this one:

select p.name, len(m.definition)
from sys.procedures p
join sys.sql_modules m on m.object_id=p.object_id
order by len(m.definition) desc
Ocaso Protal
  • 19,362
  • 8
  • 76
  • 83