Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Find Largest size Tables in a Database Expand / Collapse
Author
Message
Posted Thursday, July 10, 2008 2:18 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 26, 2012 12:43 PM
Points: 1,330, Visits: 455
Comments posted to this topic are about the item Find Largest size Tables in a Database

Cheers,
Hari
Tips & Tricks for SQL BI Developers
Post #531959
Posted Thursday, August 14, 2008 8:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 21, 2014 6:31 AM
Points: 23, Visits: 58
I tried running this code but code contains syntax errors.
would you please correct and resubmit.

Thanks

Muhammad
Post #552772
Posted Thursday, August 14, 2008 9:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 11:57 AM
Points: 3, Visits: 53
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.
Thanks
John O
Post #553246
Posted Thursday, August 14, 2008 9:21 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 7:26 AM
Points: 4,390, Visits: 9,536
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:

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 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 a3.name, a2.name

The above can be modified to show the data in any order you need.


Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #553248
Posted Friday, August 15, 2008 12:19 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 21, 2014 6:31 AM
Points: 23, Visits: 58
Mr. Jeffrey Williams, many thanks to you sir for a fine post. It works great for me.
Much obliged.

Muhammad Gigani

Post #553700
Posted Friday, August 15, 2008 12:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 11:57 AM
Points: 3, Visits: 53
Nice... learn something new every day!
Thanks!!!!!
Post #553711
Posted Friday, August 15, 2008 1:23 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 12:39 AM
Points: 3,136, Visits: 11,494
You can use this script with any version of SQL from 7.0 up.

Script to analyze table space usage
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762
Post #553735
Posted Sunday, August 17, 2008 11:40 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 26, 2012 12:43 PM
Points: 1,330, Visits: 455
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_LargestTables
GO

CREATE PROC sp_LargestTables(@n int = NULL,@IsSystemAllowed bit = 0)
AS
/*=========================================================================
CREATE DATE : Hari N Sharma
CREATION DATE : 10-09-2007
LAST MODIFICATION DATE : 11-10-2007

PURPOSE : 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 0
END

GO


Cheers,
Hari
Tips & Tricks for SQL BI Developers
Post #554095
Posted Monday, August 18, 2008 8:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 21, 2014 6:31 AM
Points: 23, Visits: 58
Great! Thanks.
Post #554345
Posted Sunday, October 12, 2008 7:53 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, February 18, 2013 1:11 AM
Points: 478, Visits: 128
This was very useful... the SP by Hari.Sharma, and the reports tip by Williams.. Thanks for both of you guys
Post #584547
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse