MSSQL script to find Largest Size Tables in a Database

Sometimes we need to check that which tables  are largest in size. Below is the store procedure script can retrieve the list of largest size tables from the selected database.

Store Procedure Script

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
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

How to run above store procedure:

1. If you want the entire 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

The output of this store procedure would be like this:

MS SQL script to find Largest Size Tables in a Database

 

 

 


<<Click here to see all posts>>

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

Prove You Are Human Time limit is exhausted. Please reload CAPTCHA.