[MSSQL] Cursor 사용예제

USE master

DECLARE @DBNAME nvarchar (256)

DECLARE @QUERY1 nvarchar (2000)

DECLARE @QUERY2 nvarchar (2000)

DECLARE @QUSEMASTER nvarchar (2000)

--생성

   

set @QUSEMASTER = 'USE MASTER'

DECLARE tnames_cursor CURSOR

FOR SELECT [name]

FROM sysdatabases

WHERE name like 'ez%'

ORDER BY [name]

-- 활성화

OPEN tnames_cursor

   

--SELECT @@CURSOR_ROWS

   

--반환

FETCH NEXT FROM tnames_cursor INTO @DBNAME

--SELECT @@FETCH_STATUS

   

WHILE (@@FETCH_STATUS = 0)

BEGIN

        set @QUERY1 = 'select * from '+@DBNAME+'.dbo.sysobjects'        

EXEC SP_EXECUTESQL @QUERY1

   

FETCH NEXT FROM tnames_cursor INTO @DBNAME

END

   

-- 해제

CLOSE tnames_cursor

   

-- 비활성화

DEALLOCATE tnames_cursor

   

-- Cursro의정보

SELECT @@CURSOR_ROWS

SELECT @@FETCH_STATUS