SQL Cursor

USE TEST;
GO
DECLARE @SchemaName varchar(255)
DECLARE @TableName  varchar(255)
DECLARE @IndexName  varchar(255)
DECLARE @Fragmentation float
DECLARE TableCursor CURSOR FOR
    SELECT SCHEMA_NAME(CAST(OBJECTPROPERTYEX(i.object_id,'Schemaid') As int)),
           OBJECT_NAME(i.object_id),
           i.name,
           ps.avg_fragmentation_in_percent
    FROM sys.dm_db_index_physical_stats(DB_ID(),null,null,null,null) as ps
    JOIN sys.indexes as i
         on ps.object_id=i.object_id
         AND ps.index_id=i.index_id
    WHERE  avg_fragmentation_in_percent >30
OPEN TableCursor
FETCH NEXT FROM Tablecursor INTO @SchemaName,@TableName,@IndexName,@Fragmentation
WHILE @@FETCH_STATUS=0
BEGIN
      PRINT @SchemaName+'.'+@TableName+'.'+@IndexName+'is'+CAST(@Fragmentation AS varchar)
            +'%Fragmetation'
     FETCH NEXT FROM TABLECURSOR INTO @SchemaName,@TableName,@IndexName,@Fragmentation
END
CLOSE Tablecursor
DEALLOCATE TableCursor

如果能用表标量,临时表,尽量不用游标,尤其是服务器端

SET NOCOUNT ON
USE TEST;
GO
IF OBJECT_ID('dbo.Nums') IS NOT NULL
   DROP TABLE dbo.T1;
Go
CREATE TABLE dbo.Nums(n int,a int)
INSERT dbo.Nums SELECT 1,2
UNION ALL SELECT 3,4
UNION ALL SELECT 7,8
GO

IF OBJECT_ID('dbo.T1') IS NOT NULL
   DROP TABLE dbo.T1;
GO
SELECT n as keycol,CAST('a' AS CHAR(200))AS filler
into dbo.T1
FROM dbo.Nums;
CREATE UNIQUE CLUSTERED INDEX idx_keycol ON dbo.T1(keycol);
DBCC DROPCLEANBUFFERS;
SELECT keycol,filler FROM dbo.T1;
go

DECLARE  @keycol AS INT,@filler AS Char(200);
DECLARE c CURSOR SCROLL FOR SELECT keycol,filler FROM dbo.T1;
OPEN C
FETCH FIRST FROM C INTO @keycol,@filler;
WHILE @@FETCH_STATUS=-1
BEGIN
  --PROCESS DATA HERE
    FETCH FIRST FROM C INTO @keycol,@filler;
END
CLOSE C;
DEALLOCATE C;
SELECT @KEYCOL,@FILLER

这里有几个地方注意 FETCH FIRST(LAST) FROM C INTO @keycol,@filler,会是@@FETCH_STATUS为-1,所以这个全局变量不能乱用的