Size of each table per database per instance in one code

Question

Size of each table per database per instance in one code

joonzz

SSC Rookie

Points: 28
More actions
December 2, 2011 at 2:54 am

#247187

Hello,
I' m looking for t-sql code to obtain all tables size within instance.
I tried to look on internet and found couple of script to get list of all tables per instance, but cant find size of each table per database plus per instance in one code.
Thank you in advance for your help.:-)
Regards
J.

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

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

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

Query sys.dm_db_index_physical_stats. There's a column page_count. A page is 8kb.

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

joonzz SSC Rookie Points: 28 More actions · Answer 2

Thanks for your input.

Im looking for something like this : sp_msforeachdb 'select "?" AS db, * from [?].sys.tables' plus SIZE of particular talbes.

Thanks.

J.

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

Why? The DMV I mentioned is instance-wide and can give you the page count per table and index. You can use Object_Name with the second parameter to get the table name.

If you want the rest of the details then you'll have to use sp_MSForEachDB. You can still use the DMV, just join it to sys.tables.

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

This should do the trick:

http://www.sqlservercentral.com/Forums/FindPost986064.aspx

-- Gianluca Sartori