Table estimated Size...Database Estimated size

Question

Viewing 5 posts - 1 through 4 (of 4 total)

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

ps. One Orange Chip Points: 29422 More actions · Answer 1

There are many scripts here. you may want to start with this one.

To find the estimate table size there is a formula

rows per page = 8096/row size

row size = fixed data size+ variable data size + null bitmap + row header

variable data size = 2+(number of variable cols X2)+max var size

null bitmap= 2+((number of columns +7)/8)

you can then find the table space requirement accordingly

number of pages=number of rows/rows per page

total size = number of pages X 8 KB

EDIT - Row header is always 4 bytes

Pradeep Singh

SQL Dev-938873 Ten Centuries Points: 1280 More actions · Answer 2

That link gives an error.."The specified request cannot be executed from current Application Pool"

not opening.

GaNeSH

ps. One Orange Chip Points: 29422 More actions · Answer 3

you can find estimated table size using the formula i mentioned.

Yep i too tried to run that query and gave that error.

you can query this to get row count and size only for each table from the example in that link

select * from #SpaceUsedByObjectForDpage

Pradeep Singh

hi_abhay78 SSCertifiable Points: 6265 More actions · Answer 4

If i am not wrong you are looking for the current size of all the objects in a Database and current size of all the tables in a database .

You can try this :

sp_msforeachtable 'sp_spaceused "?"'

[You need to run this in each database]

sp_msforeachdb '?.dbo.sp_spaceused'

[Run this in any database and it will show you the size of all the databases]

Regards

Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)