Rudy Rodarte . com

Information

This article was written on 01 Dec 2010, and is filled under SQL Blogs.

Current post is tagged

, ,

Determine Tables Sizes in SQL Server

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

Rudy

Rudy Rodarte is a SQL Server Database professional based in Austin, TX. Over his career, Rudy has worked with SSRS, SSIS, performance tuning troubleshooting. When away from Keyboard, Rudy goes to Spurs and Baylor Bear sporting events.

More Posts - Website - Twitter