May 10, 2010

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


0 comments:

Post a Comment

Author Profile

Total Pageviews

Categories

Followers

 

Sponsors