Everyone is worried about their database size. Even though storage is relatively cheap, if your database is growing linearly or exponentially, you’re going to run into issues down the road. One quick way to check things out is to look at your table sizes. I looked all over the web and found Mitch Sellers’ blog with several great SQL Table Size scripts. Here are 2 of the good ones from the comments:
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"
This method is quick, but not very clean. You’ll get a separate result for each table, so you really cannot sort. Plus, I like using Excel, so this isn’t export friendly, either.
Mitch initial post is great as long as your tables are in the dbo schema. But, if you have tables in other schemas, Mitch’s script will not display any information. However, I searched through the comments and found what I was looking for. Mark Breen (Twitter?)posted exactly what I was looking for. I’ve modified his script a bit for my use, which is found below:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF OBJECT_ID('[GetAllTableSizes]') IS NULL BEGIN EXEC ('CREATE PROCEDURE [GetAllTableSizes] as select 1') END; GO ALTER PROCEDURE GetAllTableSizes AS BEGIN /* Obtains spaced used data for ALL user tables in the database */ SET NOCOUNT ONDECLARE @TableName VARCHAR(100) --For storing values in the cursor--Cursor to get the name of all user tables from the sysobjects listing DECLARE tableCursor CURSOR FOR SELECT s.name + '.'+ o.name FROM dbo.sysobjects o INNER JOIN sys.schemas s ON o.uid = s.schema_id WHERE OBJECTPROPERTY(id, N'IsUserTable') = 1 FOR READ ONLY --A procedure level temp table to store the results CREATE TABLE #TempTable ( tableName VARCHAR(100), numberofRows VARCHAR(100), reservedSize VARCHAR(50), dataSize VARCHAR(50), indexSize VARCHAR(50), unusedSize VARCHAR(50) ) --Open the cursor OPEN tableCursor --Get the first table name from the cursor FETCH NEXT FROM tableCursor INTO @TableName --Loop until the cursor was not able to fetch WHILE (@@FETCH_STATUS >= 0) BEGIN --Dump the results of the sp_spaceused query to the temp table INSERT #TempTable EXEC sp_spaceused @TableName --Get the next table name FETCH NEXT FROM tableCursor INTO @TableName END --Get rid of the cursor CLOSE tableCursor DEALLOCATE tableCursor --Select all records so we can use the reults SELECT * FROM #TempTable ORDER BY numberofRows DESC --Final cleanup! DROP TABLE #TempTable SET NOCOUNT OFF END |
