Thursday, October 31, 2013

How to get Table Size in a Data base

The query makes use of SP_SPACEUSED and give the data.

Create  proc Table_space_used AS

BEGIN
CREATE TABLE #Temp (Name VARCHAR(1000),Rows Int,Reserved VARCHAR(100), Data VARCHAR(100), Index_size VARCHAR(100), Unused VARCHAR(100))
DECLARE @Temp1 TABLE (RowID INT IDENTITY(1,1), Table_Name VARCHAR(1000))
DECLARE @Cnt INT
DECLARE @Max INT
SET NOCOUNT ON
SET @Cnt = 1
DECLARE @Table_name VARCHAR(1000)
INSERT INTO @Temp1
SELECT Name FROM sysobjects WHERE Xtype = 'U'
SELECT @Max = MAX(RowID) FROM @Temp1
WHILE @Cnt <= @Max
BEGIN

            SELECT @Table_name= Table_name FROM @Temp1 WHERE RowID = @Cnt
            INSERT INTO #Temp

            EXEC SP_SPACEUSED @Table_name
            SET @Cnt = @Cnt + 1

END

SELECT Name,Rows, cast((REPLACE(data, 'KB','')/1024) as BIGINT)+cast((REPLACE(Index_size, 'KB','')/1024) as BIGINT) AS 'Table Data Size (MB)',
Reserved,Data,Index_size,Unused FROM #Temp ORDER BY Rows DESC
--select * from #Temp
END
go
exec Table_space_used