How to list the sizes of all tables (data and index seperately) ?

[yakcinar]

Hello,

I want to list the sizes of all tables (at least top 1000), data size and index size in seperate columns,

On DBACOCKPIT I can only display Top 500 and cannot display data and index size on the same list.

Index sizes come with single table analysis.

Is there an SQL statement that I can run on SQL Command line for this list?

Can you help, please?

Regards,

Yuksel AKCINAR

[Former Member · Accepted solution]

But you said you have SQL Commandline...

You may use this:

select top 100 --or any other top number

          left(a.name, 50) as TableName,

          left(b.name, 50) as IndexName,

          c.rowcnt as Rows,

          c.pagecnt*2 as DataKBytes,

          c.leafcnt*2 as IndexKBytes,

          c.pagecnt*2 + c.leafcnt*2 as TotalKBytes

from {YOURDB}..sysobjects a,

       {YOURDB}..sysindexes b,

       {YOURDB}..systabstats c

where b.id = a.id

   and c.id = a.id

   and c.indid = b.indid

order by 3 desc --or any other column from the result set

You can aggregate it with sum() on each numerical column and add group by left(a.name, 50), left(b.name, 50) to have it displayed in one row per one table - if you don't want break down by indices...  You might want to run sp_flushstats to flush in-memory statistics to disk first - but you will probably not be able to, so expect imprecision.

Have fun...

[sladebe]

How about using the new (and mind boggling difficult to use) sp_spaceusage system proc (note, "usage" not "used"). I find it really hard to use because the parameters are variable (you need to specify different kinds of parameters depending on earlier parameters in the parameter list and they don't use intelligent defaults) and they're not fully defined in the help pages. Also when the output says "Pages", that erroneous. The output column headers say "Pages" regardless of what units you might use (and the default units are kb which doesn't match the column headers, arrgghh). Anyway, here's an example:

[131] MYSERVER.sybsystemprocs-12:09:12-1> sp_spaceusage 'display summary','database';
All the page counts in the result set are in the unit 'KB'.
-- My note: ignore "Pages" in column headers.  It should say KB.
 TotalPages UsedPages  FreePages PctUsedPages PctFreePages DataPages  IndexPages LOBPages PctData PctIndex PctLOB
 ---------- ---------- --------- ------------ ------------ ---------- ---------- -------- ------- -------- ------
 204800.000 133360.000 71440.000        65.12        34.88 125824.000   6240.000   48.000   94.35     4.68   0.04


(1 row affected)
 TableName TotalPages UsedPages CLRPages FreePages PctUsedPages PctFreePages
 --------- ---------- --------- -------- --------- ------------ ------------
 syslogs     204800.0     816.0      0.0   55744.0         0.39        27.21


[147] MYSERVER.sybsystemprocs-12:23:34-1> sp_spaceusage 'display summary using unit=mb', 'table', '%', 'where 1=1', 'order by RsvdPages desc';
Warning: Some output column values in the result set may be incorrect. Running 'UPDATE [TABLE] STATISTICS' may help correct them.
All the page counts in the result set are in the unit 'mb'.
-- My note: ignore "Pages" in column headers.  It should say mb.
 OwnerName TableName              Type  UsedPages  RsvdPages            ExpRsvdPages PctBloatRsvdPages
 --------- ---------------------- ----- ---------- -------------------- ------------ -----------------
 dbo       sysprocedures          DATA  113.953125           115.843750        56.25            105.94
 dbo       syscomments            DATA    7.890625            10.046875        7.375             36.23
 dbo       sysprocedures          INDEX   3.984375             4.203125         2.25             46.47
 dbo       syscomments            INDEX    .796875             0.875000           .5             42.86
 dbo       sysusers               INDEX    .140625             0.750000         .375             50.00
 dbo       sysreferences          INDEX    .140625             0.750000         .375             50.00
 dbo       syspartitions          INDEX    .140625             0.625000         .375             40.00
 dbo       sysjars                INDEX     .09375             0.500000          .25             50.00
 dbo       systypes               INDEX     .09375             0.500000          .25             50.00
 dbo       sysxtypes              INDEX     .09375             0.500000          .25             50.00
 dbo       sysattributes          INDEX     .09375             0.500000          .25             50.00
 dbo       sysqueryplans          INDEX     .09375             0.500000          .25             50.00
 dbo       sysconstraints         INDEX     .09375             0.484375          .25             48.39
 dbo       syscolumns             DATA     .359375             0.375000          1.5            -75.00
 dbo       sysobjects             INDEX     .15625             0.375000          .25             33.33
 dbo       sysobjects             DATA     .203125             0.359375          .25             43.75
 dbo       syskeys                INDEX    .046875             0.250000         .125             50.00
 dbo       systypes               DATA      .03125             0.250000         .125            100.00
 dbo       sysroles               INDEX    .046875             0.250000         .125             50.00
 dbo       sysdepends             INDEX    .078125             0.250000         .125             50.00
 dbo       systabstats            DATA      .03125             0.250000         .125            100.00

[Former Member]

Hi,

Try this one:

-- ts_size.sql

set pages 49999 lin 120

col tablespace_name for a32 tru

col "Total GB"  for 999,999.9

col "GB Used"   for 999,999.9

col "GB Free"   for 99,999.9

col "Pct Free"  for 999.9

col "Pct Used"  for 999.9

comp sum of "Total GB"  on report

comp sum of "GB Used"   on report

comp sum of "GB Free"   on report

break on report

Select A.Tablespace_Name, B.Total/1024/1024/1024 "Total GB",

       (B.Total-a.Total_Free)/1024/1024/1024 "GB Used",

       A.Total_Free/1024/1024/1024 "GB Free",

       (A.Total_Free/B.Total) * 100 "Pct Free",

       ((B.Total-A.Total_Free)/B.Total) * 100 "Pct Used"

  From (Select Tablespace_Name, Sum(Bytes) Total_Free

          From Sys.Dba_Free_Space

         Group By Tablespace_Name     ) A

     , (Select Tablespace_Name, Sum(Bytes) Total

          From Sys.Dba_Data_Files

         Group By Tablespace_Name     ) B

Where A.Tablespace_Name LIKE 'ZEUS%'

  And A.Tablespace_Name = B.Tablespace_Name

Thanks,

Balaram

[shery_vijayan]

@Balram , that sounds like oracle ...

[former_member198560]

You can connect using isql and manually run the below command :

1> use database_name

2> go

1> sp_spaceused

2> go

Hope this will be helpful

~Gaurav

[yakcinar]

Hello Gaurav,

Thanks for your answer.

I only have access to SAP GUI. I don't have access to isql.

And I cannot run the  sp_spaceused proc in sql command line tool in DBACockpit.

Is there any other way to run this proc and does this give data and index sizes seperately?

Thanks.

Yuksel AKCINAR

[former_member198560]

You can execute sp_spaceused from SQL command line window. This can be opened in DBA Cockpit by clicking on "SQL Command Line" in any of the "Favourites" sections.

Then run the command using exec spaceused.

~Gaurav

[yakcinar]

Hello Gaurav,

Thanks for "SQL Command Line".

But cannot run sp_*  procedures.

Result is like this.

myhost:1> sp_spaceused
myhost:2> go

Type of command is not allowed to be executed

Regards,

Yuksel AKCINAR


[former_member188883]

Hi Yuksel,

I guess you may need to remove the restriction value 500 and execute the report from dbacockpit again to get full list of tables and indexes.

Regards,

Deepak Kori

[yakcinar]

Hello Deepak,

Thank you for your answer.

I cannot remove 500. You must select one of the 50,100 or 500.

Can you describe how to remove the restriction?

And also there is no index size in the first screen. In order to get index size of the table you must go to details of the table. This is another missing point.

Regards,

Yuksel AKCINAR