Analyzing The Size Of Database And Size Of Table

Hi Friends!

So, how we are going to find out table size information of a given database?

Here we go….

  • In this section, I am going to explain two queries regarding size. 
  • Both are related to analyzing the size of database.
  • It's always necessary to find out the size of a table due to the large amount of data.

Query 1

In this, we have to first select DATBASE from the SQL Server and fire the query so as to get the information about current size and free space of the selected database.

Let's say I have selected master database as shown below.

SQL Server

Now, execute the following query on it.

  1. --Database size information  
  2. SELECT Db_name()AS       dbname,  
  3.   name      AS     filename,  
  4.   size / 128.0 AS  currentsizemb,  
  5.   size / 128.0 - CAST(Fileproperty(name,'SpaceUsed'AS INT) / 128.0 AS freespacemb  
  6. FROM   sys.database_files   

We will get the output in the following format.

SQL Server

Query 2

This query is informative and contains almost all the required information that we require about any table.

  1. –Detailed Database size information  
  2. DECLARE  
  3. @max INT,  
  4. @min INT,  
  5. @owner NVARCHAR(256),  
  6. @table_name NVARCHAR(256),  
  7. @sql NVARCHAR(4000)  
  8.   
  9. DECLARE @table TABLE(  
  10. ident INT IDENTITY(1,1) PRIMARY KEY,  
  11. owner_name NVARCHAR(256),  
  12. table_name NVARCHAR(256))  
  13.   
  14. IF (SELECT OBJECT_ID('tempdb..#results')) IS NOT NULL  
  15. BEGIN  
  16. DROP TABLE #results  
  17. END  
  18.   
  19. CREATE TABLE #results(  
  20. ident INT IDENTITY(1,1) PRIMARY KEY--Will be used to update the owner.  
  21. table_name NVARCHAR(256),  
  22. owner_name NVARCHAR(256),  
  23. table_rows INT,  
  24. reserved_space NVARCHAR(55),  
  25. data_space NVARCHAR(55),  
  26. index_space NVARCHAR(55),  
  27. unused_space NVARCHAR(55))  
  28.   
  29. --Loop through statistics for each table.  
  30. INSERT @table(owner_name, table_name)  
  31. SELECT   
  32. su.name,   
  33. so.name  
  34. FROM  
  35. sysobjects so  
  36. INNER JOIN sysusers su ON so.uid = su.uid  
  37. WHERE  
  38. so.xtype = 'U'  
  39.   
  40. SELECT  
  41. @min = 1,  
  42. @max = (SELECT MAX(ident) FROM @table)  
  43.   
  44. WHILE @min <= @max  
  45. BEGIN  
  46.   
  47. SELECT   
  48. @owner = owner_name,  
  49. @table_name = table_name  
  50. FROM  
  51. @table  
  52. WHERE  
  53. ident = @min  
  54.   
  55. SELECT @sql = 'EXEC sp_spaceused ''[' + @owner + '].[' + @table_name + ']'''  
  56.   
  57. INSERT #results(table_name, table_rows, reserved_space, data_space, index_space, unused_space)  
  58. EXEC (@sql)  
  59.   
  60. UPDATE #results  
  61. SET owner_name = @owner  
  62. WHERE ident = (SELECT MAX(ident) FROM #results)  
  63.   
  64. SELECT @min = @min + 1  
  65. END  
  66.   
  67. SELECT * FROM #results order by table_rows   

The output of the above query is shown in the following image. We can see that the solution has size and row info.

SQL Server

That's it. I hope it will help someone learning database management.