how to find the biggest procedure in the database?

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.

Answers


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

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

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

Need Your Help

Function with if statement

c

Hi I am new in the programming. I know how a function works and if statement, so I want to know how would I be able to write an if & else statement in a function and would display the answer to...

How to get available memory C++/g++?

c++ memory-management cross-platform

I want to allocate my buffers according to memory available. Such that, when I do processing and memory usage goes up, but still remains in available memory limits. Is there a way to get available ...