SQL Server – Get table storage and row count for all tables

This simple script returns a list of tables, their row counts and storage usage

[code:tsql]

DECLARE @Table VARCHAR(255)   
CREATE TABLE #Results
(
    [Table Name] varchar(100),
    [Number of Rows] varchar(100),
    [Size Reserved] varchar(50),
    [Size Data] varchar(50),
    [Size Indexes] varchar(50),
    [Size Unused] 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
DROP TABLE #Results

GO

[/code]

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *