Find out the tables having the largest size in your database using SQL Server 2005

In one of the previous articles, we saw how to Display the size of all tables in Sql Server 2005 .

In this article, we will see how to find out the largest tables in your database and display the results in a sorted order

USE YourDB

DECLARE @TblNames Table
(
COUNTER INT IDENTITY(1,1),
tbl_name nvarchar(100) NULL,
row varchar(10),
reserved varchar(10),
data varchar(10),
index_size varchar(10),
unused varchar(10)
)
DECLARE @ROWCOUNT INT
DECLARE @I INT
DECLARE @str nvarchar(100)
SET @I = 1
INSERT INTO @TblNames(tbl_name) SELECT name FROM sys.Tables
SET @ROWCOUNT = @@ROWCOUNT
WHILE @I <= @ROWCOUNT
BEGIN
SELECT @str = tbl_name FROM @TblNames WHERE COUNTER = @I
INSERT INTO @TblNames EXEC sp_spaceused @str
SET @I = @I +1
END

-- Display results in Sorted order

SELECT tbl_name as TableNm, CAST(REPLACE(data, ' kb', '') as int) as TblSize,
CAST(REPLACE(index_size, ' kb', '') as int) as IdxSize
FROM @tblNames ORDER BY TblSize DESC

at

5 comments:

  1. AnonymousJune 10, 2008 at 2:50 PM

    I really do appreciate the information. However, I found out that I could delete the "COUNTER" row after it was assigned to the @str variable. This keeps the data from being duplicated when selecting the data afterwards during the order by. Here is the additional in-between the two lines already from your code. Hence, the DELETE row is what you were missing.

    SELECT @str = tbl_name FROM @TblNames WHERE COUNTER = @I

    DELETE FROM @TBLNAMES WHERE COUNTER = @I

    INSERT INTO @TblNames EXEC sp_spaceused @str

    Thought this might help someone, but otherwise your code was awesome! Thanks, ICE

    ReplyDelete
  2. Suprotim AgarwalJune 16, 2008 at 8:15 PM

    Hi ICE,

    Thanks for your code. I appreciate it.

    ReplyDelete
  3. AnonymousOctober 15, 2010 at 8:25 AM

    I modified your script because a lot of my tables live in schemas other than dbo....thanks for the script though worked great.

    Script:
    DECLARE @TblNames Table
    (
    COUNTER INT IDENTITY(1,1),
    tbl_name nvarchar(100) NULL,
    row varchar(10),
    reserved varchar(10),
    data varchar(10),
    index_size varchar(10),
    unused varchar(10)
    )
    DECLARE @ROWCOUNT INT
    DECLARE @I INT
    DECLARE @str nvarchar(100)
    SET @I = 1

    INSERT INTO @TblNames(tbl_name)
    SELECT s.name + '.' + st.name
    FROM sys.Tables st
    join sys.schemas s on st.schema_id = s.schema_id;

    SET @ROWCOUNT = @@ROWCOUNT

    WHILE @I <= @ROWCOUNT
    BEGIN

    SELECT @str = tbl_name
    FROM @TblNames
    WHERE COUNTER = @I;

    INSERT INTO @TblNames EXEC sp_spaceused @str;

    SET @I += 1;

    END

    -- Display results in Sorted order

    SELECT tbl_name as TableNm, CAST(REPLACE(data, ' kb', '') as int) as TblSize,
    CAST(REPLACE(index_size, ' kb', '') as int) as IdxSize
    FROM @tblNames ORDER BY TblSize DESC

    ReplyDelete
  4. Suprotim AgarwalOctober 15, 2010 at 6:24 PM

    Thanks anonymous!

    ReplyDelete
  5. AnonymousSeptember 19, 2012 at 10:54 PM

    I received "Query completed with errors" while running this query. please advise.

    ReplyDelete