July 10, 2008 at 2:18 pm
Comments posted to this topic are about the item Find Largest size Tables in a Database
August 14, 2008 at 8:37 am
I tried running this code but code contains syntax errors.
would you please correct and resubmit.
Thanks
Muhammad
August 14, 2008 at 9:11 pm
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
August 14, 2008 at 9:21 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 15, 2008 at 12:19 pm
Mr. Jeffrey Williams, many thanks to you sir for a fine post. It works great for me.
Much obliged.
Muhammad Gigani
August 15, 2008 at 12:38 pm
Nice... learn something new every day!
Thanks!!!!!
August 15, 2008 at 1:23 pm
You can use this script with any version of SQL from 7.0 up.
Script to analyze table space usage
August 17, 2008 at 11:40 pm
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')
WHEREindid 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
August 18, 2008 at 8:13 am
Great! Thanks.
October 12, 2008 at 7:53 am
This was very useful... the SP by Hari.Sharma, and the reports tip by Williams.. Thanks for both of you guys
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply