CODEWITHSUNDEEP
sqlsql-serverdatabaset-sqlsql-server-2000
Guillermo Zooby
Guillermo Zooby

Reputation: 612

How to get TOP larger tables in SQL Server 2000 and their sizes

Is there a way via T-SQL to get the larger tables and their respective size in a SQL Server 2000 engine?

Upvotes: 0

Views: 776

Answers (1)

Polux2
Polux2

Reputation: 592

I think this script works with SQL Server 2000 even if sp_msforeachtable was not documented

CREATE TABLE #SpaceUsed (
 TableName sysname
,NumRows BIGINT
,ReservedSpace VARCHAR(50)
,DataSpace VARCHAR(50)
,IndexSize VARCHAR(50)
,UnusedSpace VARCHAR(50)
)
DECLARE @str VARCHAR(500)
SET @str =  'exec sp_spaceused ''?'''
INSERT INTO #SpaceUsed
EXEC sp_msforeachtable @command1=@str

SELECT * FROM #SpaceUsed ORDER BY ReservedSpace DESC

Upvotes: 2

Related Questions