Storage Space for Table, Column, and Index

Question

Post reply

Storage Space for Table, Column, and Index

cacapo

SSC-Addicted

Points: 415
More actions
December 6, 2012 at 1:44 pm

#271577

I am building a data mart for server metrics. Using the various system views I need to find disk space used for each table in each database. The same goes for columns and indexes. Any clues how to find this?

Viewing 7 posts - 1 through 7 (of 7 total)

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

Lowell SSC Guru Points: 323546 More actions · Answer 1

sp_spacedused 'Tablename' is pretty good for at a glance info.

sp_spaceused 'MyTable'

/*

name rows reserved data index_size unused

MyTable 587 576 KB 320 KB 200 KB 56 KB

*/

did you need it for just specific items, or every table? there's some system views to get that on a database wide basis

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

cacapo SSC-Addicted Points: 415 More actions · Answer 2

I need it for every table, column, and index on all of our servers plus other things that I have already figured out how to find.

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

Try sys.dm_db_partition_stats for table and index.

Column space usage isn't stored. Best you'll be able to do is average size of the column * number of rows.

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

cacapo SSC-Addicted Points: 415 More actions · Answer 4

That's not looking too helpful either. Damn I was hoping that this would be a lot easier to find and I was just missing something.

Lowell SSC Guru Points: 323546 More actions · Answer 5

look in the scripts section; there's lots of examples where somone has wrapped sp_spacedused in a cursor,

For an example of using sys.dm_db_partition_stats that gail mentioned, if you pick apart this one:

http://www.sqlservercentral.com/scripts/Table+Size/63422/

you can get the used_page_count * page size of 8192 gets you they size in bytes; divide by whatever is good for you to get it into gig/meg/kilo.

it has tables/vs each index separately.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 6

cacapo (12/6/2012)
That's not looking too helpful either. Damn I was hoping that this would be a lot easier to find and I was just missing something.

What's hard about a DMV that lists sizes per table and index?

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