By chance anyone has a SQL command in handy which pulls out the top 10 or top 30 tables on E1/DB2
I sense that these are the big ones F4211,F0101,F4101,F0911,F4311 But need more data
Thanks in advance
By chance anyone has a SQL command in handy which pulls out the top 10 or top 30 tables on E1/DB2
I sense that these are the big ones F4211,F0101,F4101,F0911,F4311 But need more data
Thanks in advance
Define "Top".
Largest tables in number of rows?
Most transactions?
Most used Master tables?
Larry Jones
E1 9.1 - TR 9.1.5 on Win 2012 R2. Oracle DB 11.2.04
Wintel, BI Publisher
Migrating to JDE 9.2 and SQL Server 2016
On the iSeries :
select table_name, number_rows from QSYS2.SYSTABLESTAT
where table_schema = 'PRODDTA'
order by number_rows desc
On SQL Server :
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8/1024 AS TotalSpaceMB,
SUM(a.used_pages) * 8/1024 AS UsedSpaceMB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8/1024 AS UnusedSpaceMB
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
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
TotalSpaceMB desc, t.Name
On Oracle :
select owner, table_name, numrows from sys.alltables where owner = 'PRODDTA' order by numrows desc;
Last edited by altquark; 01-11-2017 at 03:31 PM.
Jon Steel
EnterpriseOne/SOA Technical Architect
erpSOURCING LLC
http://www.erpsourcing.com
cto@spla.sh
24/7 Assistance - (904) 382 5701
Thanks JOn and Larry
There are currently 1 users browsing this thread. (0 members and 1 guests)