So SQL Server has a built in SP to find out the used space by table.
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]ASDECLARE @TName VARCHAR(100)--Declare cursor to find available tables in system objectsDECLARE curtablelist CURSORFORselect [name] from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1FOR 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 nameCREATE TABLE #TableListTemp(TName varchar(100), NofR varchar(100), ResSize varchar(50), DataSize varchar(50), IndexSize varchar(50), FreeSize varchar(50))-- We are opening cursor hereOPEN curtablelist--Get the first table name from the cursorFETCH NEXT FROM curtablelist INTO @TName--Loop until the cursor was not able to fetchWHILE (@@Fetch_Status >= 0)BEGIN--Dump the results of the sp_spaceused query to the temp tableINSERT #TableListTempEXEC sp_spaceused @TName--Get the next table nameFETCH NEXT FROM curtablelist INTO @TNameEND--now close the cursorCLOSE curtablelist-- deallocate the used memory by cursorDEALLOCATE curtablelist--retrieve all the records from the temp tableSELECT * FROM #TableListTemp--Drop the temp table free up the memoryDROP TABLE #TableListTemp
Here is the code to execute the SP