To get the size of all the tables in a database, create a following stored procedure using the database you need,
CREATE PROCEDURE GetAllTableSizes
AS
/*
Obtains spaced used data for ALL user tables in the database
*/
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 results
SELECT *
FROM #TempTable
--Final cleanup!
DROP TABLE #TempTable
GO
EXEC GetAllTableSizes
Once you created the procedure just execute it and you will get the results as in the picture below.
Number of rows: The total number of rows that each table has in a database.
Reserved size: The total amount of space allocated by the table
Data size: Total amount of space used by data.
Index size: Total amount of space used by indexes.
Unused space: Total amount of space reserved for the objects, yet to be used.
database_size will always be larger than the sum of reserved and unallocated space because it includes the size of log files, but reserved and unallocated_space consider only data pages.
To find the log space, use the DBCC command,
DBCC SQLPERF ( LOGSPACE )
It will tell you the log size in MB and log space used in %
Pages that are used by XML indexes and full-text indexes are included in index_size for both result sets. When objname is specified, the pages for the XML indexes and full-text indexes for the object are also counted in the total reserved and index_size results.
When updateusage is specified, the SQL Server Database Engine scans the data pages in the database and makes any required corrections to the sys.allocation_units and sys.partitions catalog views regarding the storage space used by each table. There are some situations, for example, after an index is dropped, when the space information for the table may not be current. updateusage can take some time to run on large tables or databases. Use updateusage only when you suspect incorrect values are being returned and when the process will not have an adverse effect on other users or processes in the database. If preferred, DBCC UPDATEUSAGE can be run separately.
