Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 62
  • Last Modified:

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
0
jamesmetcalf74
Asked:
jamesmetcalf74
4 Solutions
 
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
 
Scott PletcherSenior 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
ManjuIT - 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
 
Eugene ZCommented:
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 JonesData 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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now