sql Table Sizes

--adapted from: http://www.mitchelsellers.com/blogs/articletype/articleview/articleid/121/determing-sql-server-table-size.aspx

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 
--original:
/*
	Select [name]
	From dbo.sysobjects 
	Where  OBJECTPROPERTY(id, N'IsUserTable') = 1
*/
--updated 2011-06-14 to allow cross-database/schema execution:
	Select
		TableName = 
			CASE schema_id
				WHEN 5 THEN 'MySchema.' + [name]
				ELSE [name]
			END
	From MyDatabase.sys.objects
	Where is_ms_shipped = 0
	And [type] = 'U'
	And [name] != 'dbo.tbl_ProblemTable'--this table causes problems
	Order By TableName Asc
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 results
SELECT
	TableName,
	NumberOfRows,
	ReservedSize,
	DataSize,
	IndexSize,
	UnusedSize
FROM #TempTable
ORDER BY Cast(Replace(DataSize,' KB','') As int) Desc --order by int version of the size

--Final cleanup!
DROP TABLE #TempTable
Lists all tables and their sizes within a database.

Updated: Wednesday 6th July 2011, 06:06pm

There are 0 comments

Leave a comment of your own

Comments are currently closed.