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
It's Very informative blog and useful article thank you for sharing with us , keep posting learn more about BI Tools
ReplyDeleteTableau Online Training
I really enjoyed reading. Thanks for sharing.
ReplyDeletewww.lge.com.pl