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

SQL

Comments [0] |