How to get table size information for supported databases

  • 7006931
  • 27-Sep-2010
  • 27-Jul-2018

Environment

Novell ZENworks 10 Configuration Management
Novell ZENworks 11 Configuration Management

Situation

How to get a list of table sizes in order, to troubleshoot growing tables.

Resolution

--Table row counts for Sybase
select table_name, count from systab where creator = (select uid from sysusers where name = 'zenadmin') order by count desc, table_name asc;
 
-- Check table row counts MS SQL
SELECT
    [TableName] = so.name,
    [RowCount] = MAX(si.rows)
FROM
    sysobjects so,
    sysindexes si
WHERE
    so.xtype = 'U'
    AND
    si.id = OBJECT_ID(so.name)
GROUP BY
    so.name
ORDER BY
    2 DESC
GO
 
--Check table row counts Oracle
select table_name, num_rows from user_tables where num_rows is not null order by num_rows desc, table_name asc;

Additional Information

another option to get size of table and space on disk.

Sybase:

SELECT table_name ,
    count,                    
    (table_page_count * DB_PROPERTY('PageSize')) tablesize                               
FROM SYSTAB

Oracle:

SELECT table_name,
  TRUNC(SUM(bytes)/1024) SIZE_IN_KB,
  (SELECT num_rows
  FROM user_tables z
  WHERE z.table_name=t.table_name
  AND rownum        <2
  ) NUMBER_OF_ROWS
FROM
  (SELECT segment_name table_name,
    bytes
  FROM user_segments
  WHERE segment_type IN ('TABLE','TABLE PARTITION','TABLE SUBPARTITION')
  UNION ALL
  SELECT i.table_name,
    s.bytes
  FROM user_indexes i,
    user_segments s
  WHERE s.segment_name = i.index_name
  AND s.segment_type  IN ('INDEX','INDEX PARTITION','INDEX SUBPARTITION')
  UNION ALL
  SELECT l.table_name,
    s.bytes
  FROM user_lobs l,
    user_segments s
  WHERE s.segment_name = l.segment_name
  AND s.segment_type  IN ( 'LOBSEGMENT','LOB PARTITION','LOB SUBPARTITION')
  UNION ALL
  SELECT l.table_name,
    s.bytes
  FROM user_lobs l,
    user_segments s
  WHERE s.segment_name = l.index_name
  AND s.segment_type   IN ( 'LOBINDEX','INDEX PARTITION','INDEX SUBPARTITION')
  ) t
GROUP BY table_name
ORDER BY SUM(bytes) DESC;

MSSQL:

SELECT
t.Name AS TableName,
p.rows AS RowCounts,
CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB,
CAST(ROUND((SUM(a.total_pages) - SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB,
CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
GROUP BY t.Name, s.Name, p.Rows
ORDER BY Used_MB desc, RowCounts desc , s.Name, t.Name
GO

Feedback service temporarily unavailable. For content questions or problems, please contact Support.