List Large Tables

An enlarged DotNetNuke database can affect performance and also be an indication of a more serious issue. Listing table sizes can help to understand and resolve issues.

I have used this piece of SQL to check whether the site log or the event log is over sized on a website.

CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp

The SQL may be executed from either within the DotNetNuke website or, where the website is failing, from the SQL Management Studio.

To run the SQL on the website, as a host user open the page Host > SQL.

List large tables host sql pagePaste the above code into the box and click on Run Script at the bottom of the page.

list-large-tables-host-sql-page-paste-sqlThe tables within the database will be listed together with their associated size, see example listing below:

list-large-tables-host-sql-page-run-scriptAs can be seen the tables from the database are listed  showing their name; row count; column count and data size, listed with the data size descending.