How Large Are My Tables?

Sometimes I am beating around on a SQL script that I really want to share with others. A couple of scripts I wrote to show the largest tables in a database and the largest tables in all databases on a SQL server is a perfect example.

The scripts make extensive use of the SQL 2005/2008 dynamic management view sys.dm_db_partition_stats. This view displays information about the space used to store data in a database. A concatenation method gives us the three-part name of the table and a simple JOIN brings in the Object Librarian name for the table. The results can be sorted either by row count or size in megabytes by commenting or uncommenting the ORDER BY clause.

The scripts are particularly useful for EnterpriseOne systems to identify tables that are candidates for archiving or tables that need maintenance/purging done. An example is the F98865, a table containing Work Flow Processes that should be purged periodically. With these scripts you can see what the largest tables in your SQL Server databases are.



For E1 Databases - Returns Object Librarian Description

Since we are primarily concerned with EnterpriseOne tables, the first version of the largest tables script is for a single database that is used by JD Edwards. A JOIN is used to bring in the Object Librarian Description from F9860.

--SQL Script begin
USE databasename
GO
IF DB_NAME() LIKE 'JDE%'
BEGIN
SELECT DB_NAME() + '.' + sys.schemas.name + '.' + sys.objects.name as 'SQL Object Name',
a.SIMD as 'Object Librarian Description', st.row_count as 'Rows', CAST(sum (reserved_page_count) * 8.0 / 1024 as DEC (38,2)) as 'Object Size (MB)'
FROM sys.objects
JOIN sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
FULL JOIN OPENDATASOURCE(

'SQLOLEDB',

'Data Source=sqlserverhostingJDE812database;integrated security=sspi'

).JDE812.OL812.F9860 a on (sys.objects.name = a.SIOBNM COLLATE SQL_Latin1_General_CP1_CI_AS)
JOIN sys.dm_db_partition_stats st on sys.objects.object_id = st.object_id
WHERE sys.objects.type = 'U' and st.index_id in (0,1)
GROUP BY sys.schemas.name, sys.objects.name, a.SIMD, st.row_count
ORDER BY st.row_count DESC
--ORDER BY sum (reserved_page_count) * 8.0 / 1024 DESC
END
ELSE
PRINT 'Non-EnterpriseOne Database'
--SQL Script end

As configured this script will return a results set containing a three-part object name, the Object Librarian name, the number of rows and the size taken by each table's data and indexes.

A sample of the results:

SQL Object Name	Object Librarian Description	Rows	Object Size (MB)
JDE_DEVELOPMENT.TESTCTL.F98865	Task Instance	2649997	5065
JDE_DEVELOPMENT.TESTDTA.F0911	Account Ledger	2080214	5289
JDE_DEVELOPMENT.TESTDTA.F00165	Media Objects storage	1183096	2702
JDE_DEVELOPMENT.TESTCTL.F98860	Process Instance	1060162	2290

Again, the results can be sorted either by the row count (default) or object size. Simply change the commenting in the code to change the sort.


For Non-E1 Databases

Since we cannot join non-EnterpriseOne databases on the Object Librarian Object Name we can simply leave that portion out and still get the other information:

--SQL Script begin
USE databasename
GO
SELECT DB_NAME() + '.' + sys.schemas.name + '.' + sys.objects.name as 'SQL Object Name', st.row_count as 'Rows', CAST(sum (reserved_page_count) * 8.0 / 1024 as DEC (38,2)) as 'Object Size (MB)'
FROM sys.objects
JOIN sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
JOIN sys.dm_db_partition_stats st on sys.objects.object_id = st.object_id
WHERE sys.objects.type = 'U' and st.index_id in (0,1)
GROUP BY sys.schemas.name, sys.objects.name, st.row_count
ORDER BY st.row_count DESC
--ORDER BY sum (reserved_page_count) * 8.0 / 1024 DESC
--SQL Script end

This yields similar results:

SQL Object Name	Rows	Object Size (MB)
ARCDTA.dbo.F42199	15483088	25242
ARCDTA.dbo.F42119	2420972	4345
ARCDTA.dbo.F4074	2184022	553
ARCDTA.dbo.F49219	814320	399

For all E1 Databases on a SQL Server

In some cases we may wish to gain insight into all EnterpriseOne table sizes in every JD Edwards database. For this one we create a temporary table, insert the results from the same query above but wrapped inside SQL Server's undocumented sp_MSforeachdb stored procedure, then read that temporary table.

In this case, since we are only interested in EnterpriseOne databases we specify that we only want database names like JDE%. This admittedly is a little kludgy and will not work on some releases but you are always free to change my code to your liking.

--SQL Script begin
CREATE TABLE #Results
([SQL Object Name] varchar (200),
[Object Librarian Description] varchar (60),
Rows int,
[Object Size (MB)] DEC (38,2))
GO
INSERT INTO #Results
EXEC sp_MSforeachdb @command1 = 'USE [?];
IF DB_Name() LIKE ''JDE%''
BEGIN
SELECT DB_NAME() + ''.'' + sys.schemas.name + ''.'' + sys.objects.name ,
a.SIMD, st.row_count, sum (reserved_page_count) * 8.0 / 1024
FROM sys.objects
JOIN sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
FULL JOIN OPENDATASOURCE(

'SQLOLEDB',

'Data Source=sqlserverhostingJDE812database;integrated security=sspi'

).JDE812.OL812.F9860 a on (sys.objects.name = a.SIOBNM COLLATE SQL_Latin1_General_CP1_CI_AS)
JOIN sys.dm_db_partition_stats st on sys.objects.object_id = st.object_id
WHERE sys.objects.type = ''U'' and st.index_id in (0,1)
GROUP BY sys.schemas.name, sys.objects.name, a.SIMD, st.row_count
END'
GO
SELECT * FROM #Results
ORDER BY Rows DESC
--ORDER BY [Object Size (MB)] DESC
GO
DROP TABLE #Results
--SQL Script end

The results will look exactly like the above but will contain data from all E1 databases ordered by row count. Uncomment/comment the ORDER BY clauses to sort by Size.


For All Databases on a SQL Server

Finally, we get to the script that gives us table sizes and rows for each and every database on a SQL server. Since the results could contain both E1 and non-E1 objects we leave out the Object Librarian Description.

--SQL Script begin
CREATE TABLE #Results
([SQL Object Name] varchar (200),
Rows int,
[Object Size (MB)] DEC (38,2))
GO
INSERT INTO #Results
EXEC sp_MSforeachdb @command1 = 'USE [?];
SELECT DB_NAME() + ''.'' + sys.schemas.name + ''.'' + sys.objects.name, st.row_count, sum (reserved_page_count) * 8.0 / 1024
FROM sys.objects
JOIN sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
JOIN sys.dm_db_partition_stats st on sys.objects.object_id = st.object_id
WHERE sys.objects.type = 'U' and st.index_id in (0,1)
GROUP BY sys.schemas.name, sys.objects.name, st.row_count'
GO
SELECT * FROM #Results
ORDER BY Rows DESC
--ORDER BY [Object Size (MB)] DESC
GO
DROP TABLE #Results
--SQL Script end

The results from this script will appear a bit odd since they will contain a mix of E1 and non-E1 tables:

SQL Object Name	Rows	Object Size (MB)
Allegro.dbo.dbaudit	41948619	9715
Allegro72_Test.dbo.dbaudit	38409689	8905
Allegro80_BPC_Arch.dbo.physicalquantity	10241635	2273
JDE_DV812.DV812.F980021	4886400	11472

There you are - a couple of scripts to give one some insight into the size of tables in their databases, both EnterpriseOne and non-EnterpriseOne.