Table List By Size - DESC

----- List of Big Tables
USE master
GO

IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'sp_show_huge_tables' AND type = 'P')
DROP PROC sp_show_huge_tables
GO

CREATE PROC sp_show_huge_tables
(
@top int = NULL,
@include_system_tables bit = 0
)
AS
/*************************************************************************************************
Copyright © 1997 - 2002 Narayana Vyas Kondreddi. All rights reserved.

Purpose: To list the size of all tables in the database in descending order (that is biggere tables first).
Output can be restricted to top n tables. That is you can pass the value 3 to @top parameter to
see the top 3 biggest tables in your database. Optionally, you can use @include_system_tables
parameter, to include systemt tables in the output.
NOTE: Always create this procedure in the master database and call it from the required databases,
as if the stored procedure is created in that database (That is, don't prefix the stored procedure
with the database name).

Written by: Narayana Vyas Kondreddi
http://vyaskn.tripod.com

Tested on: SQL Server 7.0

Date created: January-22-2002 21:37 GMT

Date modified: February-18-2002 11:31 GMT

Email: vyaskn@hotmail.com

Examples:

To list all the user tables in the database along with their sizes:
EXEC sp_show_huge_tables

To see the top three biggest tables in your database:
EXEC sp_show_huge_tables 3

To list all the user AND system tables in the database along with their sizes:
EXEC sp_show_huge_tables @include_system_tables = 1


To see the top three biggest user or system tables in your database:
EXEC sp_show_huge_tables 3, 1
*************************************************************************************************/

BEGIN
IF @top > 0
SET ROWCOUNT @top

SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AND s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)] FROM
(
SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS [Table Name],
CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) * (SELECT low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E')) / 1024.)/1024.)) AS [Total space used (MB)]
FROM sysindexes i (NOLOCK)
INNER JOIN
sysobjects o (NOLOCK)
ON
i.id = o.id AND
((@include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U') AND
((@include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') = 0))
WHERE indid IN (0, 1, 255)
GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))

) as a
ORDER BY [Total space used (MB)] DESC


SET ROWCOUNT 0
END

GO

GRANT EXEC ON sp_show_huge_tables TO Public


/*
Usage:
To list all the user tables in the database along with their sizes:
EXEC sp_show_huge_tables

To see the top three biggest tables in your database:
EXEC sp_show_huge_tables 3

To list all the user AND system tables in the database along with their sizes:
EXEC sp_show_huge_tables @include_system_tables = 1


To see the top three biggest user or system tables in your database:
EXEC sp_show_huge_tables 3, 1

*/