Listing Table Sizes
December 24, 2010 – 11:20Databases are a pain in the neck to look after, poorly designed models and processes that don’t remove temporary data can cause a database to grow in size. A database that is allowed to grow large beyond its requirements becomes a burden on the nightly backup, takes longer to restore in the event of a recovery scenario and slows down the development process by preventing developers from testing things out on “live” data.
More often than not I have found that the problem lies with log or analytic tables sometimes this information is liberally logged (which it should be) and then totally ignored without a thought for trimming the data on a regular basis.
SQL Server Management Studio provides a way of looking at the storage usage of tables individually from the properties context menu item of the table.
In large databases this can be laborious, I found a script that will collect this information and present it as a table. I have adapted it a little so that I can see the total size of the table and sort by each column to drill down to the problem tables.
SET NOCOUNT ON CREATE TABLE #spaceused ( name nvarchar(120), ROWS CHAR(11), reserved VARCHAR(18), DATA VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18) ) DECLARE TablesFromSysObjects CURSOR FOR SELECT name FROM sysobjects WHERE TYPE='U' ORDER BY name ASC OPEN TablesFromSysObjects DECLARE @TABLE VARCHAR(128) FETCH NEXT FROM TablesFromSysObjects INTO @TABLE WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO #spaceused EXEC sp_spaceused @TABLE FETCH NEXT FROM TablesFromSysObjects INTO @TABLE END CLOSE TablesFromSysObjects DEALLOCATE TablesFromSysObjects SELECT name AS TableName, ROWS AS ROWS, CAST(LEFT(reserved, LEN(reserved) - 3) AS INT) AS Reserved, CAST(LEFT(DATA, LEN(DATA) - 3) AS INT) AS DATA, CAST(LEFT(index_size, LEN(index_size) - 3) AS INT) AS IndexSize, CAST(LEFT(unused, LEN(unused) - 3) AS INT) AS Unused, (CAST(LEFT(reserved, LEN(reserved) - 3) AS INT) + CAST(LEFT(DATA, LEN(DATA) - 3) AS INT) + CAST(LEFT(index_size, LEN(index_size) - 3) AS INT) + CAST(LEFT(unused, LEN(unused) - 3) AS INT)) AS Total FROM #spaceused ORDER BY Total DESC DROP TABLE #spaceused |
