How to find the TOP 10 largest tables in your UIM Database

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

In some cases it may be necessary to verify which are the tables that have grown the most in your UIM backend database.  How can I find the TOP 10 largest tables?

Environment

CA UIM any version

Microsoft SQL Server or MySql Server

Resolution

-- The following query provides TOP 10 largest tables in UIM Database on Microsoft SQL Server:

SELECT TOP 10

    t.NAME AS TableName,

    i.name as indexName,

    sum(p.rows) as RowCounts,

    sum(a.total_pages) as TotalPages,

    sum(a.used_pages) as UsedPages,

    sum(a.data_pages) as DataPages,

    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,

    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB,

    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB

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

WHERE

    t.NAME NOT LIKE 'dt%' AND

    i.OBJECT_ID > 255 AND  

    i.index_id <= 1

GROUP BY

    t.NAME, i.object_id, i.index_id, i.name

ORDER BY

       SUM(p.rows) DESC

 

-- The following query provides TOP 10 largest tables in UIM on Oracle:

select * from (
   select
      owner,
      segment_name,
      bytes/1024/1024 meg
   from
      dba_segments
   where
      segment_type = 'TABLE'
   order by
      bytes/1024/1024 desc)
WHERE
   owner = 'UIM' and
   rownum <= 10;

 

-- The following query provides TOP 10 largest tables in UIM on MySQL:

SELECT CONCAT(table_schema, '.', table_name),

CONCAT(ROUND(table_rows / 1000000, 2), 'M') rows,

CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') DATA,

CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') idx,

CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,

ROUND(index_length / data_length, 2) idxfrac

FROM   information_schema.TABLES

ORDER  BY data_length + index_length DESC

 

LIMIT  10;