Have you thought, how big a table really is in your database? You know there are a million rows in the table, but how much space is that really taking?
SQL Server provides a built-in stored procedure that you can run to easily show the size of a table, including the size of the indexes.
Output will be like this,
Or in management studio: Right Click on table -> Properties -> Storage
Use below script to list all tables in a database and their row counts and sizes
CREATE TABLE #RowCountsAndSizes (TableName NVARCHAR(128),rows CHAR(11), reserved VARCHAR(18),data VARCHAR(18),index_size VARCHAR(18), unused VARCHAR(18)) EXEC sp_MSForEachTable 'INSERT INTO #RowCountsAndSizes EXEC sp_spaceused ''?'' ' SELECT TableName,CONVERT(bigint,rows) AS NumberOfRows, CONVERT(bigint,left(reserved,len(reserved)-3)) AS SizeinKB FROM #RowCountsAndSizes ORDER BY NumberOfRows DESC,SizeinKB DESC,TableName DROP TABLE #RowCountsAndSizes
Output will like this,
SQL Server doesn’t always keep disk usages up to date. Fortunately there is a DBCC command which updates these figures. Just run this command before the script above to make sure the figures are accurate :
If you found any of the information on this page helpful in anyway then please consider sharing this content with your favorite social network or by leaving your thoughts in the comment section. Thanks!