Monday 7 November 2011

SQL Server, how to return the size and rows count for all tables in a database

The script that will return the size and number of rows for all user tables in a database.


DECLARE @Table VARCHAR(255)    
CREATE TABLE #Results
(
    [TableName] varchar(100),
    [RowsCount] varchar(100),
    [SizeReserved] varchar(50),
    [SizeData] varchar(50),
    [SizeIndexes] varchar(50),
    [SizeUnused] varchar(50)
)
 
-- Get all user tables
DECLARE cursorTableList CURSOR
FOR SELECT [name]
 FROM dbo.[sysobjects] 
 WHERE  OBJECTPROPERTY(id, N'IsUserTable') = 1
FOR READ ONLY
 
OPEN cursorTableList
FETCH NEXT FROM cursorTableList INTO @Table
WHILE (@@Fetch_Status >= 0)
BEGIN
    INSERT  #Results EXEC sp_spaceused @Table
    FETCH NEXT FROM cursorTableList INTO @Table
END
CLOSE cursorTableList
DEALLOCATE cursorTableList
-- RETURN RESULTS
SELECT * FROM #Results ORDER BY CAST([RowsCount] AS INT) DESC
DROP TABLE #Results
GO

No comments:

Post a Comment