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

Document ID : KB000010095
Last Modified Date : 14/02/2018
Show Technical Document Details
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?

 

Background:

 

 

Environment:
CA UIM 8.x.xMicrosoft SQL Server MySql Server
Instructions:

-- 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/ information_schema schema on MySQL Server:

 

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;