Finding the size of each table in a SQL Server database

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.

One thought on “Finding the size of each table in a SQL Server database

  1. Pingback: Calculating SQL Table and Row Physical Sizes » the Void

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>