How to find the size of each Stored procedure located in a Database and Total size of Stored Procedures in a database.

Question

How to find the size of each Stored procedure located in a Database and Total size of Stored Procedures in a database.

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
May 29, 2010 at 6:17 am

#225084

Can anyone please provide me the script to get the below details:
1. Size of each stored procedure
2. Total size of stored procedures in each database
Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What do you mean by size? No of characters? No of lines? Something else?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 2

Actually,

sp_spaceused only takes the 'table name' as parameter input. Can I get something similar results for SPs?

Actually....I got the mail from Application Support team. Few databases they want to drop, before dropping they want the list of SPs, their source codes which I already sent across to them.

Their 3rd and 4th points was the one which I posted. I strongly believe, likewise table size concern, for SPs nothing such details would be available. Still, thought to post one question, to get some feedback...

Thanks.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Sourav-657741 (5/29/2010)
sp_spaceused only takes the 'table name' as parameter input. Can I get something similar results for SPs?

Not really, and I honestly don't see why you'd need to. Tables store data and hence can be varying sizes depending on row size and row count, whereas a stored procedure just consists of the text that defines it. The size of a stored procedure is simply the size of it's definition script.

Few databases they want to drop, before dropping they want the list of SPs, their source codes which I already sent across to them.

Query sys.procedures for the names of all procedures in the DB. If you want the definition too, join in sys.sql_modules

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 4

You're absolutely correct. I too believe the same. Will pass across the comments to Dev.

Thanks.

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 5

Sourav-657741 (5/30/2010)
You're absolutely correct. I too believe the same. Will pass across the comments to Dev.

Gosh... why can't you just script the procs out to individual files and then do a DIR to give them all the file sizes?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 6

I've already taken 'Script out' of all the SPs thru 'Generate Script' and taken the list thru 'Export List'.

Thanks.

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 7

Sourav-657741 (5/30/2010)
I've already taken 'Script out' of all the SPs thru 'Generate Script' and taken the list thru 'Export List'.

So you're all set then, right?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 8

SQL_Hunt

SSC-Dedicated

Points: 33518

May 31, 2010 at 9:08 am

#1174526

Yup. Thanks Buddy..!

Thanks.