Search by Stored Procedure size

Question

Post reply

Search by Stored Procedure size

Farrell Keough

SSCoach

Points: 17414
More actions
April 4, 2006 at 1:30 pm

#112788

I have done a search here as well as BOL, but I cannot seem to find a function to show me our SP's by size, (e.g., 12 KB). Does anyone know of such a function?
tia!
Farrell
I wasn't born stupid - I had to study.

Viewing 15 posts - 1 through 15 (of 30 total)

You must be logged in to reply to this topic. Login to reply

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 1

Here's on that does it by largest proc in length....

select

O.name, Sum(Len(S.Text)) from

sysobjects O

inner

join syscomments S on O.ID = S.ID

Group by O.name

Order by Sum(Len(S.Text)) Desc

Farrell Keough SSCoach Points: 17414 More actions · Answer 2

Thanks, but it errored.

Server: Msg 1540, Level 16, State 1, Line 1

Cannot sort a row of size 8098, which is greater than the allowable maximum of 8094.

I can do this with tables and get their size, but I cannot seem to find this for SP's. If I am understanding this correctly, it is checking the entire length of all lines of text in the SP? What I was hoping for was the KB size...

Any other suggestions?

I wasn't born stupid - I had to study.

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

How about scripting them into individual files and checking file size ?!?!

**ASCII stupid question, get a stupid ANSI !!!**

Farrell Keough SSCoach Points: 17414 More actions · Answer 4

Yeah, but there are like thousands, so I was hoping for some internal function, (quick and dirty select, you know?). Short of something nice like that, I may have to go that route...

Thanks

I wasn't born stupid - I had to study.

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 5

I belive you could use scptxfr.exe to script out the procs to a folder and then use dir via xp_cmdshell to insert details into table.

noeld SSC Guru Points: 96590 More actions · Answer 6

This works for me:

select object_name(id) n, sum(datalengh(text)) char_cnt

from syscomments

group by n

order by char_cnt desc

* Noel

Farrell Keough SSCoach Points: 17414 More actions · Answer 7

What version of SQL Server are you on? I cannot use alias's for GROUP BY or ORDER BY. I still get the same length error...

This is a bummer... I'm not even sure I can use Sue's suggestion as varchar is limited to 8000 as well...

aaarrrgggghhhh....

(btw. I am not the DBA, so I do not believe I can use "scptxfr.exe to script out the procs to a folder and then use dir via xp_cmdshell to insert details into table." Good idea, though! That would have made for an easy sort...)

I wasn't born stupid - I had to study.

sushila SSC-Dedicated Points: 35293 More actions · Answer 8

reminds me of some commercials I've seen lately.."what frequency are you on" - to echo farrell - what version of SQL Server are you using Noel ?!

**ASCII stupid question, get a stupid ANSI !!!**

Farrell Keough SSCoach Points: 17414 More actions · Answer 9

103.5

(We're SQL Server 2000 - I should have been more specific - oh, dopey me...)

I wasn't born stupid - I had to study.

Sergiy SSC Guru Points: 110206 More actions · Answer 10

I've used copy-paste into my QA for the first suggestion - it works without any problem.

SQL Server 2000 SP4.

What's wrong with your SQL Server?

_____________
Code for TallyGenerator

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 11

OK,

Your error in my script would have to have been caused by having a value in the syscomments table that was too long.....here is a modified version that will work regardless.....

Select B.[Name], B.TLength

From (

select O.name, Sum(Len(S.Text))/1024 as TLength from

sysobjects O

inner join syscomments S on O.ID = S.ID

--Where O.Name like 'Something'

Group by O.name

) B

Order By TLength Desc

Also, I converted the length to KB for you, using the assumption of 1 byte is one character, after testing to see the length of a file having 100 characters, 1000 characters, etc.....it is 1 for 1.....

Farrell Keough SSCoach Points: 17414 More actions · Answer 12

It's not mine... [Wrong with it? I can't type that much ]

I am working with many 'legacy' SP's that are WAY too long. I have come up with a better solution to handle these long ones, but I need to determine which one's fit this category so I can assign the work.

(We have many codes with muliple prorations of $'s and some of the solutions over the years got out-of-hand.)

I wasn't born stupid - I had to study.

Sergiy SSC Guru Points: 110206 More actions · Answer 13

TEXT column in syscomments has type NVARCHAR(4000).

It cannot be too long.

_____________
Code for TallyGenerator

Farrell Keough SSCoach Points: 17414 More actions · Answer 14

Same #$% error! I even tried altering your suggestion to see if dividing the length first would help, but not good...

SELECT SubSelect.[Name], SubSelect.TLength

FROM( SELECT so.[Name], SUM(( LEN( sc.[Text]) / 1024)) AS TLength

FROM sysobjects so

INNER JOIN syscomments sc ON( so.[ID] = sc.[ID])

GROUP BY so.[Name]) SubSelect

ORDER BY SubSelect.TLength DESC

(I also tried DATALENGTH...)

Thank you all sooo much for your input. I will be very appreciative if you keep this in the back of your head and see if you can come up with some type of solution. Looking up these puppies visually or by our by Log on requirements will take me a lifetime...

I wasn't born stupid - I had to study.