Calculating table size

Calculating table size

• Anthony Yott

  Hi Jorge,

  You can try the following script on SQL 2000 / 2005

  CREATE TABLE #SpaceUsed (name sysname,rows bigint,reserved sysname,data sysname,index_size sysname,unused sysname)

  DECLARE @Counter int
  DECLARE @Max int
  DECLARE @Table sysname

  SELECT name, IDENTITY(int,1,1) ROWID
  INTO #TableCollection
  FROM sysobjects
  WHERE xtype = 'U'
  ORDER BY lower(name)

  SET @Counter = 1
  SET @Max = (SELECT Max(ROWID) FROM #TableCollection)

  WHILE (@Counter <= @Max)
  BEGIN
  SET @Table = (SELECT name FROM #TableCollection WHERE ROWID = @Counter)
  INSERT INTO #SpaceUsed
  EXECUTE sp_spaceused @Table
  SET @Counter = @Counter + 1
  END

  SELECT * FROM #SpaceUsed

  DROP TABLE #TableCollection
  DROP TABLE #SpaceUsed

  Regards

  
  

• rajas

  I apologize for the delay in response, I have not setup the alerts properly.

  We are somewhat limited in how we can load data into the ReportViewer control in Management Studio. There are two ways the control supports an interactive experience.

  • Drill-in
    This is what we do today. We load in all of the data we need to render the report and show/hide information giving a simulated interactive experience.
    
  • Drill-through
    This is what we want to do in the future. We would collect a small amount of information, initially. Then we would allow the user to trigger more advanced data collection and load a new report. The ReportViewer control supports this, but we currently do not support it in Management Studio.

  I have just posted a blog entry on the disk usage report. You can download the .RDL file and modify it however you'd like so that it works better for you in your enterprise environment.

  http://blogs.msdn.com/sqlrem/archive/2006/05/16/SSMS_Reports_2.aspx

  Thanks for your feedback,

  Paul A. Mestemaker II
  Program Manager
  Microsoft SQL Server
  http://blogs.msdn.com/sqlrem/

  
  
  

• mrjoeclark

  Since you already have the tables, with rows of real data, I think you can do better than an estimation tool. 

   

  Take the current table size:

   

  EXEC sp_spaceused 'yourTable'

   

  ... then get the number of rows:

   

  SELECT COUNT(*)

  FROM yourTable

   

  ... and divide.  This will give you a much more realistic estimate than any tool can.

   

  
  --
  Adam Machanic
  Pro SQL Server 2005, available now
  http://www..apress.com/book/bookDisplay.html bID=457
  --

   

   

  <Jorge Gonzalez@discussions.microsoft.com> wrote in message news:acc81bad-c554-4d2b-8289-fb1482786568@discussions.microsoft.com...

  I have a BIG database with 8 tables growing rapidly. I would like to estimate the size of a row in disk space so I can predict how big my bd will grow in time. I know the math to calculate this but I'm look for a tool to automate this task. A tool that takes into accout calculating index size aside from table size.

  I would really apreciate any help on this.

  regards

  
  

• Analyst

  Hi Paul,

   

  I'll check out your webcast, but I'd just like to give a bit of feedback on the reports themselves.  Although I really love the look and feel and depth of content of these reports, I find that they're too heavy to run on a busy production server -- especially one with a lot of databases and/or a lot of tables.  Would it be possible to modify the reports for a future release such that the drill-down data is "lazy loaded" on demand   This would make the reports a lot more useable for enterprise scenarios.

   

  
  --
  Adam Machanic
  Pro SQL Server 2005, available now
  http://www..apress.com/book/bookDisplay.html bID=457
  --

   

   

  <Paul Mestemaker - MSFT@discussions.microsoft..com> wrote in message news:7fb4862e-7192-496e-b484-e1547a88728d@discussions.microsoft.com...

  The Disk Usage report in SQL Server Management Studio shows you the current size of your database breaking down the statistics by table, row size, index, partitions, etc.

  I'm putting together a blog series on Management Studio reports. In the first post, I put together a 2 minute demo of how to view the reports and export them to Microsoft Excel 2007.

  http://blogs.msdn.com/sqlrem/archive/2006/05/16/SSMS_Reports_1.aspx

  -Paul

  
  

• DotNET_Guru

  The Disk Usage report in SQL Server Management Studio shows you the current size of your database breaking down the statistics by table, row size, index, partitions, etc.

  I'm putting together a blog series on Management Studio reports. In the first post, I put together a 2 minute demo of how to view the reports and export them to Microsoft Excel 2007.

  http://blogs.msdn.com/sqlrem/archive/2006/05/16/SSMS_Reports_1.aspx

  -Paul

  
  
  
• Calculating table size