Find Largest Size Tables in a Database

Below is the stored procedure to return largest tables of a database.

IF OBJECT_ID('sp_LargestTables' ,'P') IS NOT NULL
DROP PROC sp_LargestTables
GO
/***************************************************************
CREATE BY : Hari Sharma
PURPOSE : To get a list of tables according to their size.
***************************************************************/
CREATE PROC sp_LargestTables
(
   @n int = NULL,
   @IsSystemAllowed bit = 0
)
AS
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 (NOLOCK)
      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


How to use:

1. If you want all the user tables in the database with largest db size then:
EXEC sp_LargestTables [No Need to pass parameters]

2. If you want only 3 tables in the database with largest db size then:
EXEC sp_LargestTables 3

3. If you want only 20 tables in the database with largest db size including system tables then:
EXEC sp_LargestTables 20,1