Cursor Syntax

Quick reference for cursor code.


-- Prints the row count of every user table in the database
DECLARE @SQLString NVarchar(1024)
DECLARE @TableName NVarchar(64)
DECLARE @ParameterList NVarchar(1024)
DECLARE @RowCountOutput INT

Declare GenericCursor CURSOR for Select NAME From SysObjects where Xtype = 'U' Order by NAME
Open GenericCursor
Fetch GenericCursor into @TableName
While (@@Fetch_Status = 0)
 BEGIN
   Set @ParameterList = '@MyOutput INT Output'
   Set @SQLString = 'SET @MyOutput = (Select COUNT(*) From ' + QuoteName(@TableName) + ')'
   EXEC SP_EXECUTESQL @SQLString, @ParameterList, @MyOutput = @RowCountOutput OUTPUT
   Print @TableName + ' Has ' + Convert(NVarchar, @RowCountOutput) + ' Rows.'
   Fetch GenericCursor into @TableName
 END
Close GenericCursor
Deallocate GenericCursor
go

-- Cursor Statements Requiring a GO

DECLARE @SQLString NVarchar(1024)
DECLARE @TableName NVarchar(64)

Declare GenericCursor CURSOR for Select NAME From SysObjects where Name like 'TblZS%' ORDER BY Name
Open GenericCursor
Fetch GenericCursor into @TableName
While (@@Fetch_Status = 0)
 BEGIN
   Set @SQLString = 'SP_RENAME ' + '''' + @TableName + '''' + ', ' + '''' + '_' + @TableName + '''' + CHAR(13) + ' GO' 
   PRINT @SQLString
   Fetch GenericCursor into @TableName
 END
Close GenericCursor
Deallocate GenericCursor
go