# Monday, June 20, 2011

SQL Server: Getting table size for all tables

Here is a script for getting size information on every table in your SQL Server database. Based on a script by Mitchell Sellers, this script also works if you have multiple schemas in your database:

DECLARE @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 '['+SCHEMA_NAME(schema_id)+'].['+name+']'
 AS SchemaTable
 FROM sys.tables where type_desc = 'USER_TABLE'
FOR READ ONLY
 
--A procedure level temp table to store the results
CREATE TABLE #TempTable
 (
     id int identity,
     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
     UPDATE #TempTable set tableName = @TableName where id = @@identity 
     --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 results
SELECT * 
FROM #TempTable
ORDER BY tableName
 
--Final cleanup!
DROP TABLE #TempTable
 
GO
#    Comments [0] |