[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


size of tables in a sql database

Posted on 2016-08-03
Medium Priority
Last Modified: 2016-08-08
Does anyone have a query that can tell me the size of individual tables within one sql database.
if not size in actual data size, maybe rows?  would rather have size in megabytes though
Question by:jamesmetcalf74
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 31

Expert Comment

by:James Murrell
ID: 41740791
hello I use below , hope it helps

create table #t
name nvarchar(128),
rows varchar(50),
reserved varchar(50),
data varchar(50),
index_size varchar(50),
unused varchar(50)

declare @id nvarchar(128)
declare c cursor for
select name from sysobjects where xtype='U'

open c
fetch c into @id

while @@fetch_status = 0 begin

insert into #t
exec sp_spaceused @id

fetch c into @id

close c
deallocate c

select * from #t
order by convert(int, substring(data, 1, len(data)-3)) desc

drop table #t

Open in new window


Author Comment

ID: 41740875
unfortunately.  i am almost out of drive space because of this db and wouldnt be able to run that query
any other suggestion?
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 332 total points
ID: 41740928
Try this:

    SCHEMA_NAME(t.schema_id) AS schema_name,
    t.name AS table_name,
    --p.index_id, ISNULL(MAX(i.name), '<heap>') AS index_name,
    CAST(SUM(au.total_pages) / 128.0 AS decimal(9, 2)) AS size_mb,
    CAST(SUM(au.used_pages) / 128.0 AS decimal(9, 2)) AS used_mb,
    CAST(SUM(au.total_pages) / 128.0 AS decimal(9, 2)) -
    CAST(SUM(au.used_pages) / 128.0 AS decimal(9, 2)) AS unused_mb,
    MAX(FILEGROUP_NAME (au.data_space_id)) AS filegroup_name
FROM sys.allocation_units au
INNER JOIN sys.partitions p ON p.partition_id = au.container_id
INNER JOIN sys.tables t ON t.object_id = p.object_id
LEFT OUTER JOIN sys.indexes i ON i.object_id = t.object_id AND i.index_id = p.index_id
WHERE t.schema_id <> (SELECT schema_id FROM sys.schemas WHERE name = 'sys')
GROUP BY SCHEMA_NAME(t.schema_id), t.name --, p.index_id
--ORDER BY size_mb DESC
    'sys' AS schema_name,
    OBJECT_NAME(p.object_id) AS table_name,
    --1 AS index_id, '<internal>' AS index_name,
    CAST(SUM(p.reserved_page_count) / 128.0 AS decimal(9, 2)) AS size_mb,
    CAST(SUM(p.used_page_count) / 128.0 AS decimal(9, 2)) AS used_mb,
    CAST(SUM(p.reserved_page_count) / 128.0 AS decimal(9, 2)) -
    CAST(SUM(p.used_page_count) / 128.0 AS decimal(9, 2)) AS unused_mb,
    '<n/a>' AS filegroup_name
FROM sys.dm_db_partition_stats p
INNER JOIN sys.internal_tables it ON it.object_id = p.object_id
WHERE it.internal_type IN (202,204,207,211,212,213,214,215,216)
GROUP BY p.object_id
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.


Accepted Solution

Manju earned 1004 total points
ID: 41740950
If you are using SQL Server Management Studio (SSMS), instead of running a query (which in my case returned duplicate rows) you can run a standard report.

Right click on the database
Navigate to Reports > Standard Reports > Disk Usage By Table

Note: The database compatibility level must be set to 90 or above for this to work correctly. See http://msdn.microsoft.com/en-gb/library/bb510680.aspx
LVL 43

Assisted Solution

by:Eugene Z
Eugene Z earned 332 total points
ID: 41741578
in your SSMS select "tables" and click F7 (object Explorer details)
to see space -records per table,,

" i am almost out of drive space because..:"  
check tables that you can delete or truncate for sure
exec sp_spaceused "yourCandidateTabletoBe deleted"

also check the drive for cleanup where you mdf -ndf -ldf files

(BTW: you may have trans log file issue - > can you post some errors?)
if trans log = if DB can be in the Simple recovery- just set it and shrink..
otherwise - it needs trans log backup to keep size


also you may need to order more space for this DB drive(s)
LVL 10

Expert Comment

by:Jason clark
ID: 41741798
you can also open the 'Object Explorer Details' in SSMS

Top Level of Object Explorer Details

From the Top-Level, open the Tables folder to get a list of all the tables in your database.

You may need to customise the columns to see the Space Used. This can be done by right clicking on the header row and choosing the columns you wish to display.

Assisted Solution

by:Daniel Jones
Daniel Jones earned 332 total points
ID: 41741800
Try this
exec  sp_spaceused N'dbo.MyTable'

Open in new window

If you are using SQL Server Management Studio then you can also run a standard report
Right click on the DatabaseReportsStandard Reports > Disk Usage By Table
Note: Database compatibility level must be set to 90 or above

Author Comment

ID: 41747343
Thanks guys-
the report out of ssms worked just great

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question