How to measure table size in GB in a table in SQL

Question

In a previous question @Morawski was saying that "a table with 1,000 columns and 44,000 rows It's about 330 MB; that's how much a browser uses for just a few open tabs".

How many columns and rows the table should have to tell its size is > 10 GB (suposing the table has only double values).

How did @Morawski concluded that 1,000 columns and 44,000 is 330MB?

Is there any script that could tell this in SQL?

In the previous question, all the columns held doubles. That's rather important to the computation. — Don Roby, Aug 15 '11 at 22:29

score 16 · Answer 1 · answered Aug 15 '11 at 22:29

16

There is a sproc call sp_spaceused. Don't know if this is what @Morawski used but as an example on a dev db I had handy:

exec sp_spaceused 'aspnet_users'

gives

name          rows    reserved     data      index_size     unused
------------- ------- ------------ --------  ------------   ---------- 
aspnet_Users  3       48 KB        8 KB      40 KB          0 KB

answered Aug 15 '11 at 22:29

Jon Egerton

40,401
11
97
129

Is it possible to use this store procedure to get the space used by all the tables ? – Nicolas C Oct 03 '14 at 10:32
You mean as a whole (totals) or individually? – Jon Egerton Oct 03 '14 at 10:37
Individually. I mean, using this store procedure to have a list of all the tables, with the size and space used for each table. – Nicolas C Oct 03 '14 at 10:39
That's worth a new question. Post it and link here, and I'll follow up. – Jon Egerton Oct 03 '14 at 10:45

score 10 · Accepted Answer · edited Nov 29 '11 at 19:36

-- Measures tables size (in kilobytes)
-- Tested in MS SQL Server 2008 R2

declare @t table (
name nvarchar(100), [rows] int, [reserved] nvarchar(100), [data] nvarchar(100), [index_size] nvarchar(100), [unused] nvarchar(100)
)
declare @name nvarchar(100)

declare tt cursor for
Select name from sys.tables
open tt

fetch next from tt into @name
while @@FETCH_STATUS = 0
begin
  insert into @t
  exec sp_spaceused @name
  fetch next from tt into @name
end

close tt
deallocate tt

select name as table_name, [rows] as rows_count, data + [index] as total_size, data as data_size, [index] as index_size
from (select name,
[rows],
cast (LEFT(data, LEN(data)-3) as int) data,
cast (LEFT(index_size, LEN(index_size)-3) as int) [index]
 from @t
) x
order by 3 desc, 1

For better performance, make the cursor `STATIC`: `DECLARE tt CURSOR STATIC FOR...` — Anton Shepelev, Jan 27 '16 at 14:04

score 3 · Answer 3 · answered Aug 15 '11 at 22:53

Not sure about the TSQL script (I'm sure it exists), but you can find it through the UI (SSMS) as follows:

1) R-click the table
2) ...Properties
3) ...Storage tab

From there, it will tell you both the "data space" and the "index space" -- so if you want a total footprint, just add those up.

EDIT
Consider also log space if you're looking for a total footprint for the table.

Here is info on the stored procedure listed in @jon's answer. Also, it references the sys views where you can query the space usage data directly. http://msdn.microsoft.com/en-us/library/ms188776.aspx

Remus Rusanu · Answer 4 · 2011-08-16T05:08:58.747

There are precise formulas to do capacity planning for SQL Server:

With 1000 columns of fixed length doubles (that would be the float(53) SQL type, 8 bytes of storage) your row approaches the max row size limit, but it actually fits in page. 44k rows require 44k pages (due to the huge row size, only one row per page would fit), that is, at 8kb a page 44000*8kb = ~344 Mb. If you have a clustered index size would increase depending on the key size, see the first link above.

But a table design of 1000 columns is a huge code smell. Your question is very vague about the database part, your previous question never mentions a database and is about in memory arrays, when added together these two questions just don't make much sense.

Perhaps you are interested in reading about Sparse Columns, about EAV modeling or about XML data type.

score 1 · Answer 5 · edited Apr 12 '22 at 20:05

To find the size of all tables in the database, you can use the undocumented stored procedure sp_MSforeachtable.

From SQL Shack:

There have always been some undocumented objects in SQL Server that are used internally by Microsoft, but they can be used by anybody that have access to it. One of those objects is a stored procedure called sp_MSforeachtable.

sp_MSforeachtable is a stored procedure that is mostly used to apply a T-SQL command to every table, iteratively, that exists in the current database.

Here's how you would use it:

sp_MSforeachtable 'exec sp_spaceused [?]'

How to measure table size in GB in a table in SQL

5 Answers5

Linked