CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008-r2
Chad
Chad

Reputation: 24679

Space used by tables

Does this procedure accurately show the space used in a db? I'm doubting the results. 

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 [name]
from dbo.sysobjects 
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 tablename

--Final cleanup!
DROP TABLE #TempTable

Sorry about the formating of this post. StackO sure is buggy - no formatting toolbar today.

Upvotes: 3

Views: 4246

Answers (2)

Ralph Shillington
Ralph Shillington

Reputation: 21098

You may wish to consider using one of the system's dynamic views / functions

For example, consider this simple query using sys.dm_db_index_physical_stats to return much more detailed information on the pages used for heaps clustered and non-clustered indexes:

select * from sys.dm_db_index_physical_stats ( 
    DEFAULT 
  , DEFAULT 
  , DEFAULT 
  , DEFAULT 
  , 'DETAILED'
)

Upvotes: 1

Paul Karlin
Paul Karlin

Reputation: 840

Your code gives a table-by-table view of space used. You can also just run sp_spaceused without params to get an overview of the size of the whole database. What causes you to doubt the results?

Upvotes: 1

Related Questions