Is a Temporary Table Really Necessary? - PART III:

One of the tricks I use is to mimic the main reason a cursor is usually built for, looping through a result set one row at a time and performing an action based on the data in that row.

Below is a short query that displays this logic by obtaining all the user table names and executing sp_spaceused on each table.

SET NOCOUNT ON
DECLARE @lngTabCount INTEGER
DECLARE @lngLoopCount INTEGER
DECLARE @strTabName SYSNAME

CREATE TABLE #tTables
(numID INTEGER IDENTITY(1,1),strTableName SYSNAME)

INSERT INTO #tTables (strTableName)
SELECT name
FROM dbo.sysobjects
WHERE xtype = 'u'

SET @lngTabCount = @@ROWCOUNTSET ,@lngLoopCount = @lngTabCount

WHILE @lngLoopCount <> 0BEGINSET
@strTabName = (SELECT strTableName FROM #tTables WHERE numID = @lngLoopCount)EXEC sp_spaceused @strTabNameSET @lngLoopCount = @lngLoopCount - 1
ENDDROP TABLE #tTables
GO

Cursor-like actions without cursor overhead and performance related problems.