Solved

size of tables in a sql database

Posted on 2016-08-03
8
57 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
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
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
ID: 41740875
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:Scott Pletcher
Scott Pletcher earned 83 total points
ID: 41740928
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Accepted Solution

by:
Manju earned 251 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
0
 
LVL 42

Assisted Solution

by:Eugene Z
Eugene Z earned 83 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
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 9

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.
0
 
LVL 4

Assisted Solution

by:Daniel Jones
Daniel Jones earned 83 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
0
 

Author Comment

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

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

726 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