Determing SQL Server Table Size
Posted by Tim on September 3, 2010 in SQL Server |
This is a bit of code I found that determines the size used by each table in your database.
Make sure you run DBCC UPDATEUSAGE first to correct any incorrect stats (pages etc) on your tables.
DBCC UPDATEUSAGE (YOUR DATABASE NAME)
DECLARE @TableName VARCHAR(100) --For storing values in the cursor
--Cursor to get the name of all user tables from the sysobjects listing
DECLARE tableCursor CURSOR
FOR
select [name]
from dbo.sysobjects
where OBJECTPROPERTY(id, N'IsUserTable') = 1
FOR READ ONLY
CREATE TABLE #TempTable
(
tableName varchar(100),
numberofRows varchar(100),
reservedSize varchar(50),
dataSize varchar(50),
indexSize varchar(50),
unusedSize varchar(50)
)
OPEN tableCursor
FETCH NEXT FROM tableCursor INTO @TableName
WHILE (@@Fetch_Status >= 0)
BEGIN
INSERT #TempTable
EXEC sp_spaceused @TableName
FETCH NEXT FROM tableCursor INTO @TableName
END
CLOSE tableCursor
DEALLOCATE tableCursor
--Select all records so we can use the reults, ordered by biggest
SELECT *
FROM #TempTable
ORDER BY CAST(LEFT(dataSize,LEN(dataSize)-3) AS NUMERIC(18,0)) DESC
DROP TABLE #TempTable
--Cursor to get the name of all user tables from the sysobjects listing
DECLARE tableCursor CURSOR
FOR
select [name]
from dbo.sysobjects
where OBJECTPROPERTY(id, N'IsUserTable') = 1
FOR READ ONLY
CREATE TABLE #TempTable
(
tableName varchar(100),
numberofRows varchar(100),
reservedSize varchar(50),
dataSize varchar(50),
indexSize varchar(50),
unusedSize varchar(50)
)
OPEN tableCursor
FETCH NEXT FROM tableCursor INTO @TableName
WHILE (@@Fetch_Status >= 0)
BEGIN
INSERT #TempTable
EXEC sp_spaceused @TableName
FETCH NEXT FROM tableCursor INTO @TableName
END
CLOSE tableCursor
DEALLOCATE tableCursor
--Select all records so we can use the reults, ordered by biggest
SELECT *
FROM #TempTable
ORDER BY CAST(LEFT(dataSize,LEN(dataSize)-3) AS NUMERIC(18,0)) DESC
DROP TABLE #TempTable
References:
-
-
-
CodeLab Twitter- codelabnz: @possiblyb9 I now have blackberry for breakfast February 7, 2012
- codelabnz: I have now joined most ppl in indonesia and got myself a blackberry February 7, 2012
- codelabnz: From a bb February 6, 2012
- codelabnz: @bartekmarnane some info on micro orms http://t.co/UDfv5ML7 February 5, 2012
- codelabnz: @bartekmarnane depends on the project, but i found u have 2 be very careful with EF and the type of SQL it generates February 5, 2012
- codelabnz: @bartekmarnane I have been using EF for awhile, good for building apps quickly, but found it performs bad on higher traffic web applications February 5, 2012
- codelabnz: stuff it, I give up on Entity Framework, going to a micro ORM, back to writing SQL queries! I would rather do this than have a slow app! February 5, 2012
- codelabnz: Been too busy for tweeting lately January 21, 2012
- codelabnz: Blog Post: Setting the screen position when visiting previous page with jQuery http://t.co/evuKxVv0 January 17, 2012
- codelabnz: is anyone using Godaddy Cloud hosting? can someone provide their website so I could do a speed test? January 9, 2012