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.
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