I needed to work out how to get the size of each table in a DotNetNuke database to work out why the DB was 3.9gb in size. I found a stored procedure from The Right Stuff which gives me the details on the database and each table within it using the undocumented sp_msForEachTable stored procedure:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE checksize AS BEGIN SET NOCOUNT ON DBCC UPDATEUSAGE(0) -- DB size. EXEC sp_spaceused -- Table row counts and sizes. CREATE TABLE #t ( [name] NVARCHAR(128), [rows] CHAR(11), reserved VARCHAR(18), data VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18) ) INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''' SELECT * FROM #t -- # of rows. SELECT SUM(CAST([rows] AS int)) AS [rows] FROM #t DROP TABLE #t END GO
With this I could see that the DotNetNuke search cache was getting massive as the SearchItemWord and SearchItemWordPosition were both huge.