size of tables in a sql database

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
jamesmetcalf74Asked:
Who is Participating?
 
ManjuConnect With a Mentor IT - Project ManagerCommented:
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
 
James MurrellProduct SpecialistCommented:
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
 
jamesmetcalf74Author Commented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Scott PletcherConnect With a Mentor Senior DBACommented:
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
 
Eugene ZConnect With a Mentor Commented:
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
 
Jason clarkDBA FreelancerCommented:
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
 
Daniel JonesConnect With a Mentor Data Research AnalystCommented:
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
 
jamesmetcalf74Author Commented:
Thanks guys-
the report out of ssms worked just great
0
All Courses

From novice to tech pro — start learning today.