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

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook

5 comments:

Anonymous said...: 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; June 10, 2008 at 2:50 PM
Suprotim Agarwal said...: Hi ICE,

Thanks for your code. I appreciate it.; June 16, 2008 at 8:15 PM
Anonymous said...: 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; October 15, 2010 at 8:25 AM
Suprotim Agarwal said...: Thanks anonymous!; October 15, 2010 at 6:24 PM
Anonymous said...: I received "Query completed with errors" while running this query. please advise.; September 19, 2012 at 10:54 PM