Hi Friends!
So, how we are going to find out table size information of a given database?
Here we go….
Query 1

In this, we have to first select DATABASE 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 database
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 Database Size
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)
  9. DECLARE @table TABLE(
  10. ident INT IDENTITY(1,1) PRIMARY KEY,
  11. owner_name NVARCHAR(256),
  12. table_name NVARCHAR(256))
  14. IF (SELECT OBJECT_ID('tempdb..#results')) IS NOT NULL
  15. BEGIN
  16. DROP TABLE #results
  17. END
  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))
  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'
  40. SELECT
  41. @min = 1,
  42. @max = (SELECT MAX(ident) FROM @table)
  44. WHILE @min <= @max
  45. BEGIN
  47. SELECT
  48. @owner = owner_name,
  49. @table_name = table_name
  50. FROM
  51. @table
  52. WHERE
  53. ident = @min
  55. SELECT @sql = 'EXEC sp_spaceused ''[' + @owner + '].[' + @table_name + ']'''
  57. INSERT #results(table_name, table_rows, reserved_space, data_space, index_space, unused_space)
  58. EXEC (@sql)
  60. UPDATE #results
  61. SET owner_name = @owner
  62. WHERE ident = (SELECT MAX(ident) FROM #results)
  64. SELECT @min = @min + 1
  65. END
  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 Table size
That's it. I hope it will help someone learning database management.