by Administrator
24. September 2009 21:59
This simple script returns a list of tables, their row counts and storage usage
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