Find Largest size Tables in a Database

  • Comments posted to this topic are about the item Find Largest size Tables in a Database

  • I tried running this code but code contains syntax errors.

    would you please correct and resubmit.

    Thanks

    Muhammad

  • 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

  • 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 opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Mr. Jeffrey Williams, many thanks to you sir for a fine post. It works great for me.

    Much obliged.

    Muhammad Gigani

  • Nice... learn something new every day!

    Thanks!!!!!

  • 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

  • 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

  • Great! Thanks.

  • 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 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply