MS SQL 2005 Tip: Display Estimated Sizes of All Tables in a Database
Today I needed to get a quick estimate of tables sizes in an MS SQL 2005 database. So I ended up using the sp_spaceused stored procedure, which displays the space used for either a database or a single object (table, etcetera).
As I was interested in a more detailed listing, I threw together a quick cursor that pulls all table names from the information_schema views, and inserts the results for each item into a table variable.
Updated: Updated to incorporate improvements from comments
USE MyDatabaseName
GO
--- suppress extraneous row counts
SET NOCOUNT ON
GO
--- Temporary table used to store results
DECLARE @spaceUsed TABLE
(
RecordID int IDENTITY(1,1),
TableName nvarchar(128),
Rows varchar(20),
Reserved varchar(20),
Data varchar(20),
Index_size varchar(20),
Unused varchar(20),
SchemaName nvarchar(128)
)
--- get the names of all tables in the current database
DEClARE @id int
DECLARE @tableName VARCHAR(260)
DECLARE @tableSchema VARCHAR(128)
DECLARE @tables CURSOR
SET @tables = CURSOR FOR SELECT TABLE_SCHEMA, TABLE_SCHEMA +'.'+ TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'Base Table'
OPEN @tables
FETCH NEXT FROM @tables INTO @TableSchema, @TableName
--- For each table ...
WHILE @@FETCH_STATUS = 0 -- lazy check
BEGIN
BEGIN TRY
--- insert the estimated size into the work table
INSERT INTO @spaceUsed (TableName, Rows, Reserved, Data, Index_Size, Unused)
EXEC sp_spaceused @TableName
--- update the schema information
UPDATE @spaceUsed
SET SchemaName = @TableSchema
WHERE RecordID = SCOPE_IDENTITY()
END TRY
BEGIN CATCH
--- ignore any errors
PRINT 'Unable to calculate space for table ['+ @TableName +'] '+ char(10) + ERROR_MESSAGE()
END CATCH
FETCH NEXT FROM @tables INTO @TableSchema, @TableName
END
CLOSE @tables
DEALLOCATE @tables
Since the resulting sizes are formatted as strings (ie xxx KB), I had to run a quick update to remove the non-numeric values. Then I was able to calculate the total size for each table, and sort the results numerically. So I could get an idea of which were the largest tables in my database.
It is not at all pretty. But it did get the job done.
--- remove KB prefix so values can be cast to numeric type ...
UPDATE @spaceUsed
SET Reserved = REPLACE(Reserved, ' KB', '')
, Data = REPLACE(Data, ' KB', '')
, Index_size = REPLACE(Index_size, ' KB', '')
, Unused = REPLACE(Unused, ' KB', '')
, Rows = REPLACE(Rows, ' KB', '')
--- display estimates for each table (order by largest amount used)
SELECT SchemaName
, TableName
, CAST(Rows AS decimal) AS Rows
, CAST(Reserved AS decimal) AS ReservedKB
, CAST(Data AS decimal) AS DataKB
, CAST(Index_size AS decimal) AS IndexKB
, CAST(Data AS decimal) + CAST(Index_size AS decimal) AS TotalUsedKB
, CAST(Unused AS decimal) AS UnusedKB
FROM @spaceUsed
ORDER BY TotalUsedKB DESC
--- display overall estimates for database
EXEC sp_spaceused
GO
SET NOCOUNT OFF
GO
If you are interested in listing the overall sizes for a series of MS SQL datasources, there is good example in an older entry on John Whish's blog.
7 comments:
There's also a report built-in to MSSQL 2005 that will generate a list of the diskspace of all tables.
I use the "Disk Usage by Top Tables" report a lot to get an idea of what my table storage requirements look like.
Nice one! I will have to check that one out on a machine that has reporting tools installed. (Unfortunately, my current one does not).
-Leigh
The script doesn't seem to support Schema. So only dbo.X will work, but newSchema.X will not.
@Sami,
Yes, you are right. In my case, I only had a single schema to work with. But adding in the table schema is a quick fix. Just concatenate:
TABLE_SCHEMA +'.'+ TABLE_NAME
I will update the entry to include that. Thanks!
-Leigh
Updated.
I'm sure you know this but for your readers, If you're ever interested in doing this in MySQL there's also an easy way to do it.
SELECT table_name, table_rows, data_length, auto_increment
FROM INFORMATION_SCHEMA.TABLES
WHERE table_name like ('tbl%')
This simple query provides the table name, estimated rows, size in bytes and last auto increment.
Good tip!
Post a Comment