sql cursor - need help

SELECT @SQL = @SQL + 'Select ' + quotename(NAME, '''') + ' as [DB_Name],
ps.object_id AS objectid, object_Name(PS.Object_ID,' + convert(VARCHAR(10), database_id) + ') as object_name ,
ps.index_id AS indexid,  ind.name as IndexName,sch.name as SchemaName,ps.partition_number AS partitionnum, ps.avg_fragmentation_in_percent AS frag, ps.page_count
 from ' + quotename(NAME) + '.sys.dm_db_index_physical_stats(' + convert(VARCHAR(10), database_id) + ', NULL, NULL, NULL, NULL)  ps
inner join ' + NAME + '.sys.tables as tbl
    on ps.object_id = tbl.object_id
  inner join ' + NAME + '.sys.schemas as sch
    on tbl.schema_id = sch.schema_id  
  inner join ' + NAME + '.sys.indexes as ind
    on ps.index_id = ind.index_id and
       ps.object_id = ind.object_id
WHERE ps.avg_fragmentation_in_percent > 10.0
      AND ps.index_id > 0
      AND ps.page_count > 50
ORDER BY [DB_Name] asc , ps.avg_fragmentation_in_percent DESC;
' + CHAR(13)
FROM sys.databases
WHERE state_desc = 'ONLINE'
      AND NAME NOT IN ('master', 'tempdb', 'model', 'msdb', 'ReportServer', 'ReportServerTempDB')

declare @SQL nvarchar(max)
set @SQL = ''
select @SQL = @SQL +
'Select ' + quotename(name,'''') + ' as [DB_Name], 
ps.object_id AS objectid, object_Name(PS.Object_ID,' + convert(varchar(10),database_id) + ') as object_name , 
ps.index_id AS indexid,  ind.name as IndexName,sch.name as SchemaName,ps.partition_number AS partitionnum, ps.avg_fragmentation_in_percent AS frag, ps.page_count
 from ' + quotename(name) + '.sys.dm_db_index_physical_stats(' + 
convert(varchar(10),database_id) + ', NULL, NULL, NULL, NULL)  ps 
inner join ' + name + '.sys.tables as tbl
    on ps.object_id = tbl.object_id
  inner join ' + name + '.sys.schemas as sch
    on tbl.schema_id = sch.schema_id  
  inner join ' + name + '.sys.indexes as ind
    on ps.index_id = ind.index_id and
       ps.object_id = ind.object_id
WHERE ps.avg_fragmentation_in_percent > 10.0
	AND ps.index_id > 0
	AND ps.page_count > 1
ORDER BY [DB_Name] asc , ps.avg_fragmentation_in_percent DESC;

' 
+ CHAR(13) +'---------------------------------------------'  + CHAR(13) 

 from sys.databases where state_desc = 'ONLINE'
 and name not in ('master','tempdb','model','msdb', 'ReportServer','ReportServerTempDB')
 
print @SQL

Select all Open in new window

-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid INT;
DECLARE @indexid INT;
DECLARE @partitioncount BIGINT;
DECLARE @schemaname NVARCHAR(130); 
DECLARE @objectname NVARCHAR(130); 
DECLARE @indexname NVARCHAR(130); 
DECLARE @partitionnum BIGINT;
DECLARE @partitions BIGINT;
DECLARE @frag FLOAT;
DECLARE @page_count BIGINT;
DECLARE @command NVARCHAR(4000); 
DECLARE @updateStatsThreshold INT;

DECLARE @StartTime DATETIME;
DECLARE @EndTime DATETIME;

-----------------------------------------------------------
DECLARE @Work_To_Do TABLE (
	 [DATABASE]	VARCHAR(50)
	,ObjectID NUMERIC
	,IndeID INT
	,PartitionNum INT
	,Frag FLOAT
	,Page_Count INT
)

DECLARE @Databases TABLE (
	 RowNumber INT
	,Name VARCHAR(50)
	,[dbID] INT
)

DECLARE @x INT
DECLARE @y INT
DECLARE @dbID INT

-----------------------------------------------------------
INSERT INTO
	@Databases
SELECT 
	 ROW_NUMBER() OVER (ORDER BY dbID) AS 'RowNumber' 
	,Name
	,dbID	
FROM
	master..sysdatabases

--SELECT * FROM  #Databases

-----------------------------------------------------------
SET @x = 1
SELECT @y = COUNT(*) FROM @Databases

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 
-- and convert object and index IDs to names.
WHILE @x < @y
BEGIN
	SELECT @dbID = dbID FROM @Databases WHERE RowNumber = @x
	
	INSERT INTO
		@Work_To_Do
	SELECT
		(SELECT Name FROM @Databases WHERE RowNumber = @x) AS 'Database',
		OBJECT_ID AS ObjectID,
		index_id AS IndexID,
		partition_number AS PartitionNum,
		avg_fragmentation_in_percent AS Frag,
		Page_Count
	FROM 
		sys.dm_db_index_physical_stats (@dbID, NULL, NULL , NULL, 'LIMITED')
	WHERE 
		avg_fragmentation_in_percent > 10.0 
		AND index_id > 0
		--AND page_count > 50
	ORDER BY 
		avg_fragmentation_in_percent DESC;
	PRINT @x
	SET @x = @x + 1
END

-----------------------------------------------------------
--stats threshold
SET @updateStatsThreshold = 7

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM @Work_To_Do;

-- Open the cursor.
OPEN partitions;

--write header
PRINT N'"Date", "Page_Count", "Frag", "Command"';

SET @StartTime = (SELECT GETDATE()) 

-- Loop through the partitions.
WHILE (1=1)
    BEGIN;
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag, @page_count;
        IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
        FROM sys.objects AS o
        JOIN sys.schemas AS s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;
        SELECT @indexname = QUOTENAME(name)
        FROM sys.indexes
        WHERE  OBJECT_ID = @objectid AND index_id = @indexid;
        SELECT @partitioncount = COUNT (*)
        FROM sys.partitions
        WHERE OBJECT_ID = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
--        IF @frag < 90.0
            -- SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE WITH (MaxDop=8)'; 
			--SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
        --IF @frag >= 90.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (MaxDop=8)';
        IF @partitioncount > 1
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS NVARCHAR(10));
        PRINT N'"' + CAST(GETDATE() AS VARCHAR(20)) + '", "' + CAST(@page_count AS VARCHAR(30)) + '", "' + CAST(@frag AS VARCHAR(20)) + '", "' + @command + '"';
		EXEC (@command);

--stats
		IF STATS_DATE(@objectid, @indexid) < DATEADD(dd, -@updateStatsThreshold, GETDATE())
		BEGIN
			SELECT @command = 'UPDATE STATISTICS ' + @schemaname + '.' + OBJECT_NAME(@objectid) + ' ' + @indexname +' WITH RESAMPLE'
	        PRINT N'"' + CAST(GETDATE() AS VARCHAR(20)) + '", "' + CAST(@page_count AS VARCHAR(30)) + '", "' + CAST(@frag AS VARCHAR(20)) + '", "' + @command + '"';
			EXEC (@command)
		END
--End loop
    END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

SET @EndTime = (SELECT GETDATE())

SELECT LTRIM(STR(DATEDIFF(mi,@starttime,@endtime) % 60 )) + '.' + LTRIM(STR(DATEDIFF(ss,@starttime,@endtime) % 60 ))  AS 'CompletionTime'

SELECT * FROM @Work_To_Do

Select all Open in new window

-----------------------------------------------------------
DECLARE @Work_To_Do TABLE (
	 [DATABASE]	VARCHAR(50)
	,ObjectID NUMERIC
	,IndeID INT
	,PartitionNum INT
	,Frag FLOAT
	,Page_Count INT
)

DECLARE @Databases TABLE (
	 RowNumber INT
	,Name VARCHAR(50)
	,[dbID] INT
)

DECLARE @x INT
DECLARE @y INT
DECLARE @dbID INT

-----------------------------------------------------------
INSERT INTO
	@Databases
SELECT 
	 ROW_NUMBER() OVER (ORDER BY dbID) AS 'RowNumber' 
	,Name
	,dbID	
FROM
	master..sysdatabases

--SELECT * FROM  #Databases

-----------------------------------------------------------
SET @x = 1
SELECT @y = COUNT(*) FROM @Databases

WHILE @x < @y
BEGIN
	SELECT @dbID = dbID FROM @Databases WHERE RowNumber = @x
	
	INSERT INTO
		@Work_To_Do
	SELECT
		(SELECT Name FROM @Databases WHERE RowNumber = @x) AS 'Database',
		OBJECT_ID AS ObjectID,
		index_id AS IndexID,
		partition_number AS PartitionNum,
		avg_fragmentation_in_percent AS Frag,
		Page_Count
	FROM 
		sys.dm_db_index_physical_stats (@dbID, NULL, NULL , NULL, 'LIMITED')
	WHERE 
		avg_fragmentation_in_percent > 10.0 
		AND index_id > 0
		AND page_count > 50
	ORDER BY 
		avg_fragmentation_in_percent DESC;
	PRINT @x
	SET @x = @x + 1
END

-----------------------------------------------------------	
SELECT 
	*
FROM
	@Work_To_Do	

	
	

Select all Open in new window