This example demonstrates cursor-like actions without cursor overhead and performance related problems that show up when using them.
The main reason a cursor is built is for looping through a result set one row at a time and performing an action based on the data in that row. Here is a short query that displays this logic by obtaining all the user table names and executing sp_spaceused on each table.
Also, this is using a Table Variable instead of a temporary table so note its use as well.
1: DECLARE @lngTabCount INTEGER
2: DECLARE @lngLoopCount INTEGER
3: DECLARE @strTabName SYSNAME
4:
5: DECLARE @tTables TABLE
6: (
7: numID INTEGER IDENTITY(1,1),
8: strTableName SYSNAME
9: )
10:
11: INSERT INTO @tTables (strTableName)
12: SELECT name FROM dbo.sysobjects WHERE xtype = 'u'
13:
14: SET @lngTabCount = @@ROWCOUNT
15: SET @lngLoopCount = @lngTabCount
16:
17: WHILE @lngLoopCount <> 0
18: BEGIN
19: SET @strTabName = (SELECT strTableName FROM @tTables WHERE numID = @lngLoopCount)
20: EXEC sp_spaceused @strTabName
21: SET @lngLoopCount = @lngLoopCount - 1
22: END