Wednesday, February 3, 2010

Get spaced used of all tables


DECLARE @TableName sysname
DECLARE @SpaceUsed TABLE(TableName sysname,
Rows INT,
Reserved VARCHAR(50),
Data VARCHAR(50),
IndexSize VARCHAR(50),
Unused VARCHAR(50))
DECLARE Cur CURSOR FOR
SELECT name
FROM sysobjects
WHERE type = 'U'
OPEN Cur
FETCH FROM Cur
INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @SpaceUsed
EXEC sp_spaceused @TableName
FETCH NEXT FROM Cur
INTO @TableName
END
CLOSE Cur
DEALLOCATE Cur
SELECT * FROM @SpaceUsed

No comments:

Post a Comment