SQLServerCentral / Article Discussions / Discuss content posted by ~~Hari.Bagra / Article Discussions by Author / Find Largest size Tables in a Database / Latest Posts

RE: Find Largest size Tables in a Database

Maysarah — Sun, 12 Oct 2008 07:53:13 GMT

This was very useful... the SP by Hari.Sharma, and the reports tip by Williams.. Thanks for both of you guys

RE: Find Largest size Tables in a Database

mgigani-696113 — Mon, 18 Aug 2008 08:13:03 GMT

Great! Thanks.

RE: Find Largest size Tables in a Database

Hari.Sharma — Sun, 17 Aug 2008 23:40:28 GMT

Hi,Sorry for inconvenience duw to my post.There is a little bit change in my script...I have updated it in my post.IF EXISTS ( SELECT 1 FROM master.dbo.sysobjects WHERE name = 'sp_LargestTables' AND type = 'P' )DROP PROC sp_LargestTablesGOCREATE PROC sp_LargestTables(@n int = NULL,@IsSystemAllowed bit = 0)AS/*=========================================================================CREATE DATE : Hari N SharmaCREATION DATE : 10-09-2007LAST MODIFICATION DATE : 11-10-2007PURPOSE : To get a list of User/System tables according to their size.=========================================================================*/BEGIN SET NOCOUNT ON DECLARE @LOW int SELECT @LOW = LOW FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E' IF @n > 0 SET ROWCOUNT @n SELECT TableName,[Row Count],[Size (KB)] FROM ( SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS TableName,SUM(i.rowcnt) [Row Count], CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) * @LOW) / 1024.0))) AS [Size (KB)] FROM sysindexes i INNER JOIN sysobjects o (NOLOCK) ON i.id = o.id AND ((@IsSystemAllowed = 1 AND o.type IN ('U', 'S')) OR o.type = 'U') WHERE indid IN (0, 1, 255) GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) ) AS Z ORDER BY [Size (KB)] DESC SET ROWCOUNT 0ENDGO

RE: Find Largest size Tables in a Database

Michael Valentine Jones — Fri, 15 Aug 2008 13:23:58 GMT

You can use this script with any version of SQL from 7.0 up.Script to analyze table space usage[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762[/url]

RE: Find Largest size Tables in a Database

johnny_orourke — Fri, 15 Aug 2008 12:37:59 GMT

Nice... learn something new every day!Thanks!!!!!

RE: Find Largest size Tables in a Database

mgigani-696113 — Fri, 15 Aug 2008 12:19:00 GMT

Mr. Jeffrey Williams, many thanks to you sir for a fine post. It works great for me.Much obliged.Muhammad Gigani

RE: Find Largest size Tables in a Database

Jeffrey Williams 3188 — Thu, 14 Aug 2008 21:21:25 GMT

On SQL Server 2005 and greater, you can right-click on the database in Object Explorer, select reports, standard reports and run the disk usage by tables report. There are other reports available also.The query that is used to populate this report is:[code]SELECT (row_number() over(order by a3.name, a2.name))%2 as l1, a3.name AS [schemaname], a2.name AS [tablename], a1.rows as row_count, (a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved, 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 unusedFROM (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 a1LEFT 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 a3.name, a2.name[/code]The above can be modified to show the data in any order you need.

RE: Find Largest size Tables in a Database

johnny_orourke — Thu, 14 Aug 2008 21:11:12 GMT

Also tried running the code... with the following error messages;Incorrect syntax near the keyword 'SET'Incorrect syntax near '+'Incorrect syntax near the keyword 'CONVERT'Would be awesome to be able to figure this out.ThanksJohn O

RE: Find Largest size Tables in a Database

mgigani-696113 — Thu, 14 Aug 2008 08:37:26 GMT

I tried running this code but code contains syntax errors.would you please correct and resubmit.ThanksMuhammad

Find Largest size Tables in a Database

Hari.Sharma — Thu, 10 Jul 2008 14:18:39 GMT

Comments posted to this topic are about the item [B]Find Largest size Tables in a Database[/B]