question

Naren avatar image
Naren asked

Regarding Table sizes in Database?

How to find the all user table sizes and rows count in decending order? Because I want to which table is going to use maximum size?
sql-server-2005dba
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 avatar image Blackhawk-17 commented ·
FYI: The most rows does not always mean the largest size.
2 Likes 2 ·
Usman Butt avatar image
Usman Butt answered
Hope this helps BEGIN TRY SELECT a3.name AS [schemaname] , a2.name AS [tablename] , a1.rows AS row_count , ( a1.reserved + ISNULL(a4.reserved, 0) ) * 8 AS [ReservedSizeInKB] , a1.data * 8 AS data , ( CASE WHEN ( a1.used + ISNULL(a4.used, 0) ) > a1.data THEN ( a1.used + ISNULL(a4.used, 0) ) - a1.data ELSE 0 END ) * 8 AS index_size , ( CASE WHEN ( a1.reserved + ISNULL(a4.reserved, 0) ) > a1.used THEN ( a1.reserved + ISNULL(a4.reserved, 0) ) - a1.used ELSE 0 END ) * 8 AS unused FROM ( SELECT ps.object_id , SUM(CASE WHEN ( ps.index_id < 2 ) THEN row_count ELSE 0 END) AS [rows] , SUM(ps.reserved_page_count) AS reserved , SUM(CASE WHEN ( ps.index_id < 2 ) THEN ( ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count ) ELSE ( ps.lob_used_page_count + ps.row_overflow_used_page_count ) END) AS data , SUM(ps.used_page_count) AS used FROM sys.dm_db_partition_stats ps GROUP BY ps.object_id ) AS a1 LEFT OUTER JOIN ( SELECT it.parent_id , SUM(ps.reserved_page_count) AS reserved , SUM(ps.used_page_count) AS used FROM sys.dm_db_partition_stats ps INNER JOIN sys.internal_tables it ON ( it.object_id = ps.object_id ) WHERE it.internal_type IN ( 202, 204 ) GROUP BY it.parent_id ) AS a4 ON ( a4.parent_id = a1.object_id ) INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id ) INNER JOIN sys.schemas a3 ON ( a2.schema_id = a3.schema_id ) WHERE a2.type N'S' AND a2.type N'IT' ORDER BY [ReservedSizeInKB] DESC END TRY BEGIN CATCH SELECT ERROR_NUMBER() , ERROR_SEVERITY() , ERROR_STATE() , ERROR_MESSAGE() END CATCH
7 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image Usman Butt commented ·
There is neither any transaction nor any object is created. It is just a SELECT statement. So I do not think that this would do any harm in production. But as a good DBA, you must test every code in a TEST environment before putting it into production. No one will ever claim any responsibility, if you are going to put someone's code directly into PRODUCTION environment and it somehow proves fatal. Cheers.
3 Likes 3 ·
Naren avatar image Naren commented ·
what is the output we are getting by this transaction? And I am going to run this in production so need to drop if any object is created after running this tran? Let me know the what need to do after running this tran?
0 Likes 0 ·
Naren avatar image Naren commented ·
Yes thanks a lot...
0 Likes 0 ·
Usman Butt avatar image Usman Butt commented ·
Please let us know when you are sure that it worked for you.
0 Likes 0 ·
Naren avatar image Naren commented ·
sue I will let you know... I asked this question is one database rebuild index job was failed Error: not able allocating the pages for rebuild... So I need to check what happend and which object was using much space need to know what happend exactly? Let me know do you have any answers about it? (File is in restricted growth)
0 Likes 0 ·
Show more comments
ThomasRushton avatar image
ThomasRushton answered
Data Space Analyser from Atlantis Interactive. It's free, and will generate pretty pictures. Check out Matt's other tools too.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.