Wednesday, July 7, 2010

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.

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.

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.