高分求解SQL游标问题

[bearold]

有如下一种代码，目的从库中枚举出表名与所有者，如果表的所有者是'wygl'则将之修改为'dbo'。但是代码在运行时只是不停的取出第一个表，游标并不向下走，搞不清楚是什么问题？！
代码如下：
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

Declare @table_name varchar(40),@table_schema varchar(20)
Declare table_cursor CURSOR FOR
SELECT table_name,table_schema from INFORMATION_SCHEMA.TABLES

open table_cursor

FETCH NEXT FROM table_cursor into @table_name,@table_schema
WHILE @@FETCH_STATUS = 0
PRINT @table_name+''+@table_Schema
BEGIN
if @table_schema='wygl'
BEGIN
PRINT @table_name+''+@table_Schema
EXEC sp_changeobjectowner @table_name ,'dbo'
END
FETCH NEXT FROM table_cursor into @table_name,@table_schema
END
CLOSE table_cursor
DEALLOCATE table_cursor

[bearold]

哈，Sorry，真是干晕了，自已都没注意到。谢谢，可以了。我第一次使用游标。

[paoluo]

...

[paoluo]

Declare @table_name varchar(40),@table_schema varchar(20)
Declare table_cursor CURSOR FOR
SELECT table_name,table_schema from INFORMATION_SCHEMA.TABLES

open table_cursor

FETCH NEXT FROM table_cursor into @table_name,@table_schema
WHILE @@FETCH_STATUS = 0
--PRINT @tble_name+''+@table_Schema --這句代碼的問題，將此句代碼放到BEGIN中去
BEGIN
PRINT @table_name+''+@table_Schema
if @table_schema='wygl'
BEGIN
PRINT @table_name+''+@table_Schema
EXEC sp_changeobjectowner @table_name ,'dbo'
END
FETCH NEXT FROM table_cursor into @table_name,@table_schema
END
CLOSE table_cursor
DEALLOCATE table_cursor

[paoluo]

那句代碼放在那裡，相當於WHILE @@FETCH_STATUS = 0中只有首先“PRINT @tble_name+''+@table_Schema ”這一句代碼。

游標不能移動，“WHILE @@FETCH_STATUS = 0”也就一直滿足條件，就在那死循環了.

[believe209]

学习!

[hrb2008]

Declare @table_name varchar(40),@table_schema varchar(20)
Declare table_cursor CURSOR FOR
SELECT table_name,table_schema from INFORMATION_SCHEMA.TABLES

open table_cursor

FETCH NEXT FROM table_cursor into @table_name,@table_schema
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @table_name+''+@table_Schema

if @table_schema='wygl'
BEGIN
PRINT @table_name+''+@table_Schema
EXEC sp_changeobjectowner @table_name ,'dbo'
END
FETCH NEXT FROM table_cursor into @table_name,@table_schema
END
CLOSE table_cursor
DEALLOCATE table_cursor

[中国风]

declare hcforeach cursor global
for
SELECT 'EXEC sp_changeobjectowner '+quotename(table_name,'''') +',''dbo'''
from INFORMATION_SCHEMA.TABLES where table_schema='wygl'
exec sp_msforeach_worker '?'

[中国风]

这样就OK了简单方便:
declare hcforeach cursor global
for
SELECT 'EXEC sp_changeobjectowner '+quotename('wygl.'+table_name,'''') +',''dbo'''
from INFORMATION_SCHEMA.TABLES where table_schema='wygl'
exec sp_msforeach_worker '?'

[sp4]

哈哈

[sp4]

一个while 死循环

[kafuwei]

WHILE @@FETCH_STATUS = 0
PRINT @table_name+''+@table_Schema

在这步进入死循环。