Find out the used space for a Table in SQL Server

Some time we would like to know how much space table is using to store the data on a disk.

So SQL Server has a built in SP to find out the used space by table.

sp_spaceused '‘Tablename'

once you will execute above stored procedure you will see the following result.



Actually it is very useful that how much amount of space data is occuping on the disk.


If you have a no of tables in database and you don't want to execute sp one by one then here is
the stored procedure which will retrieve all the talbe name from the system object and then will
pass one by one as a parameter to previous described sp and display the result on the screen at shot

Here is the code for SP

CREATE PROCEDURE [dbo].[dms_FindAllTableUsedSpace]

AS

DECLARE @TName VARCHAR(100)

--Declare cursor to find available tables in system objects

DECLARE curtablelist CURSOR

FOR

select [name] from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1

FOR READ ONLY

-- Create a temp table to hold the table name temporarily and then loop through all the table and run sp with that table name

CREATE TABLE #TableListTemp

(

TName varchar(100), NofR varchar(100), ResSize varchar(50), DataSize varchar(50), IndexSize varchar(50), FreeSize varchar(50)

)

-- We are opening cursor here

OPEN curtablelist

--Get the first table name from the cursor

FETCH NEXT FROM curtablelist INTO @TName

--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 #TableListTemp

EXEC sp_spaceused @TName

--Get the next table name

FETCH NEXT FROM curtablelist INTO @TName

END

--now close the cursor

CLOSE curtablelist

-- deallocate the used memory by cursor

DEALLOCATE curtablelist

--retrieve all the records from the temp table

SELECT * FROM #TableListTemp

--Drop the temp table free up the memory

DROP TABLE #TableListTemp

Here is the code to execute the SP

EXEC dms_FindAllTableUsedSpace

What Next

• Stumble it
• Digg this
• Save to Delicious
• Tweet it
• Subscribe

Related Posts