Paul Duffett – Me, myself and MS SQL Server

Hi all,

Just a very, very quick post because I’m still at work

This very short script will loop through all of your (user created) tables and return the number of rows and space used. Very useful indeed.

SET NOCOUNT OFF

DECLARE @tblName VARCHAR(255)
DECLARE tblCursor CURSOR FOR
SELECT name FROM sysobjects WHERE type = ‘U’ ORDER BY name

OPEN tblCursor

FETCH NEXT FROM tblCursor
INTO @tblName

WHILE @@FETCH_STATUS = 0
BEGIN
  
   EXEC sp_spaceused @tblName

   FETCH NEXT FROM tblCursor
   INTO @tblName
END

CLOSE tblCursor
DEALLOCATE tblCursor

Enjoy

PD