March 18, 2008 at 8:01 am
Hi,
How can we see all tables names and size of tables in (mb ) from database with query .
thaxxx
regards
jagpal singh
March 18, 2008 at 8:32 am
jagpalsingh4 (3/18/2008)
Hi,How can we see all tables names and size of tables in (mb ) from database with query .
thaxxx
regards
jagpal singh
Try this - results are in kb:
select name
, reserved = reserved * 8
, data = data * 8
, index_size = CASE WHEN data > pages THEN (data - pages) ELSE 0 END * 8
, unused = CASE WHEN reserved > data THEN (reserved - data) ELSE 0 END * 8
from
(
select
name
, sum(reserved_page_count) as reserved
, sum(used_page_count) as data
, sum(
case
when (index_id < 2) then (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
else lob_used_page_count + row_overflow_used_page_count
end
) as pages
, sum(
case
when (index_id < 2) then row_count
else 0
end
) as row_count
from sys.dm_db_partition_stats s
join sys.tables t on t.object_id = s.object_id
group by name
) as table_sizes
March 18, 2008 at 8:35 am
Something like this should help.
CREATE TABLE #temp (
table_name sysname ,
row_count int,
reserved_size varchar(50),
data_size varchar(50),
index_size varchar(50),
unused_size varchar(50)
)
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
count(*) as col_count,
a.data_size [Data_Size_KB],
1.0 * CAST(Replace(a.data_size, ' KB', '') as decimal(9,3)) / 1024 as [Data_Size_MB]
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name = b.table_name
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(Replace(a.data_size, ' KB', '') as int) desc
DROP TABLE #temp
March 18, 2008 at 8:44 am
I should point out that my method will not return correct results for tables with XML indexes or full text indexes, whereas Adam's method will return correct results. But then with that method you're effectively using a cursor so pick your poison 🙂
March 18, 2008 at 8:49 am
You can also right click the database in SSMS, choose Reports, and look for one that matches your needs. There are a number of standard reports that might help.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 18, 2008 at 8:53 am
This gives you the information you want, along with a more detailed analysis.
Script to analyze table space usage
March 18, 2008 at 8:58 am
You can also right click the database in SSMS, choose Reports, and look for one that matches your needs. There are a number of standard reports that might help.
These reports are nice and can help you identify large tables.
This gives you the information you want, along with a more detailed analysis.
Script to analyze table space usage
Good, link Michael. I saved this one 🙂
March 18, 2008 at 9:07 am
hi,
will u plz clear me this ssms, and that query is lovely will u plz tell me how to see reports from ssms .
thaxx
March 18, 2008 at 9:10 am
Right-click the database --> reports --> standard reports --> disk usage by table
March 18, 2008 at 9:23 am
hi
I have done this before but when i right click the database thn there is no option of reports .Thats y i am confused that what is ssms .and plz tell me what can i do now
thaxx for ur reply and sorry fro inconvenience
regards
jagpal singh
March 18, 2008 at 9:40 am
SSMS is SQL Server Management Studio
What service pack is your instance on? I think you need to be on service pack 2.
March 18, 2008 at 9:44 am
If you're not on the latest service pack then the reports are viewable from the summary tab in ssms. Select the summary tab, select a database and then the Report button should become visible in the summary tab toolbar.
March 18, 2008 at 9:49 am
hi,
Thaxxx now i have done this .
regards
jagpal singh
March 18, 2008 at 9:54 am
If you're not on the latest service pack then the reports are viewable from the summary tab in ssms. Select the summary tab, select a database and then the Report button should become visible in the summary tab toolbar.
Thanks Karl. I havent been on SP1 in a while and could not remember if reporting was available in SP1, without reporting services being installed.
March 19, 2008 at 11:19 am
I have done this before but when i right click the database thn there is no option of reports .Thats y i am confused that what is ssms .and plz tell me what can i do now
SSMS = SQL Server Management Studio
To see the Reports you must have SQL Server 2005 SP2 installed.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply