Stored Procedure to find huge tables with Row count in given database

Posted: April 1, 2008 in Scripts to use

/*——–Stored Procedure to find huge tables with Row count in given database————————*/

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

/*************************************************************************************************

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

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s