Solved

size of tables in a sql database

Posted on 2016-08-03
8
47 Views
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
0
Comment
Question by:jamesmetcalf74
8 Comments
 
LVL 31

Expert Comment

by:James Murrell
Comment Utility
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
end

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

0
 

Author Comment

by:jamesmetcalf74
Comment Utility
shoot-
unfortunately.  i am almost out of drive space because of this db and wouldnt be able to run that query
any other suggestion?
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 83 total points
Comment Utility
Try this:

SELECT
    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
--
UNION ALL
SELECT
    '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
--
ORDER BY size_mb DESC
0
 
LVL 6

Accepted Solution

by:
Manju earned 251 total points
Comment Utility
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
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 42

Assisted Solution

by:EugeneZ
EugeneZ earned 83 total points
Comment Utility
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
use
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)
0
 
LVL 7

Expert Comment

by:Jason clark
Comment Utility
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.
0
 
LVL 3

Assisted Solution

by:Daniel Jones
Daniel Jones earned 83 total points
Comment Utility
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 Database > Reports > Standard Reports > Disk Usage By Table
Note: Database compatibility level must be set to 90 or above
0
 

Author Comment

by:jamesmetcalf74
Comment Utility
Thanks guys-
the report out of ssms worked just great
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now