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