Solved

sql cursor - need help

Posted on 2012-12-26
24
402 Views
Last Modified: 2013-01-01
The attached sql script performs index rebuild for single DB.

We need to perform index rebuild against all DB's present on sql instance in single script.

I have tried to create cursor but it was not returning results as expected.

Please help me to create cursor for the attached script (need to perform index rebuild against all DB's present on server using attached script).

Thanks in advance. .
Index-Maintainance-Script-.txt
0
Comment
Question by:sg05121983
  • 12
  • 8
  • 3
  • +1
24 Comments
 

Author Comment

by:sg05121983
ID: 38722794
Increasing point to 500 from 250.
0
 
LVL 4

Expert Comment

by:krtyknmsql
ID: 38722872
0
 

Author Comment

by:sg05121983
ID: 38722922
Thanks Krtyknmsql,

we can't use script given in above URL. Bacause script doesn't contain update stats & page count condition and we dont want to set any filefactor.

Can you please help me to write the cursor for attached script

@Experts : please share the logic to create cursor for the attached script.
Index-Maintainance-Script-.txt
0
 
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 38722980
Can you please check what below query is returning.....


SELECT 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(DB_ID(), 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;

your code will work only if above query will return something...
0
 

Author Comment

by:sg05121983
ID: 38723035
Hi Saurv,

executed on some DB's not all DB's and the above query is returing output.

sample output:

objectid             indexid      partitionnum      frag                  page_count
356912343      1      1                    98.1818181818182      55
421576540      1      1                 98.1818181818182      55
1010102639      1      1                97.4789915966387      119
1224391431      1      1             88.659793814433      97
1157579162      1      1             66.3      1000
1074102867      4      1              22.4787363304982      823

FYI...
I have tested above code before posting my question on EE.

Now need cursor logic ti execute attached script against all DB's in sql instance.

I am DBA and i dont have devlopment knowldge of query.

Could you please help me on this.
Index-Maintainance-Script-.txt
0
 
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 38723062
Hi sg05121983 ,
sorry  I misunderstood your problem... I am working on your actual issue...will provide you solution soon...
0
 
LVL 12

Accepted Solution

by:
Saurabh Bhadauria earned 350 total points
ID: 38723444
Hi sg05121983 ,
Can you Please check below code.....

I have got all the data in temp table #work_to_do   and then running cursor on it... I have Changed few statements to dynamic .. Please have a look... and let me know if you have any concern...

--drop table #work_to_do
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 @dbName NVARCHAR(500)

--stats threshold
SET @updateStatsThreshold = 7

CREATE TABLE #work_to_do (
  [DB_name] NVARCHAR(50), 
  objectid BIGINT,
  objectname NVARCHAR(500), 
  indexid BIGINT, 
  indexName NVARCHAR(500),
  SchemaName NVARCHAR(500),
  partitionnum INT, 
  frag FLOAT, 
  page_count BIGINT )

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 > 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')

INSERT INTO #work_to_do
EXECUTE (@SQL)

SELECT *
FROM #work_to_do

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR
FOR
SELECT [db_name], objectid, objectname, indexid, indexName, SchemaName, partitionnum, frag, page_count
FROM #work_to_do
ORDER BY [Db_name] ASC, frag DESC;

-- Open the cursor.
OPEN partitions;

SET @StartTime = (
		SELECT Getdate()
		)

-- Loop through the partitions.
WHILE (1 = 1)
BEGIN
		;

	SET @partitioncount = 0

	FETCH NEXT
	FROM partitions
	INTO @dbname, @objectid, @objectName, @indexid, @indexname, @schemaName, @partitionnum, @frag, @page_count;

	IF @@FETCH_STATUS < 0
		BREAK;

	SET @command = N' SELECT @partitioncount = count(*)
	             FROM ' + @dbname + '.sys.partitions
	             WHERE object_id = @objectid
		        AND index_id = @indexid'

	EXEC sp_executesql @command, N'@objectid bigint,@indexid int,  @partitioncount int output', 
	                     @objectid = @objectid, @indexid = @indexid, @partitioncount = @partitioncount OUTPUT

	-- 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 'use ' + @dbname + ' ' + @command
	PRINT N'"' + cast(getdate() AS VARCHAR(20)) + '", "' + cast(@page_count AS VARCHAR(30)) + '", "' + cast(@frag AS VARCHAR(20)) + '", "' + @command + '"';

	EXEC ('use ' + @dbname + '  ' + @command);

	--stats
	SELECT @command = 'use ' + @dbname + '     IF STATS_DATE(@objectid, @indexid) < DATEADD(dd, - @updateStatsThreshold, getdate())   Begin '

	SELECT @command = @command + ' Begin  UPDATE STATISTICS ' + @schemaname + '.' + object_name(@objectid) + ' ' + @indexname + ' WITH RESAMPLE   End '

	EXEC sp_executesql @command, N'@objectid bigint , @indexid bigint, @updateStatsThreshold int ', @objectid = @objectid, @indexid = @indexid, @updateStatsThreshold = @updateStatsThreshold
		--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

-- Drop the temporary table.
DROP TABLE #work_to_do;

Open in new window

0
 

Author Comment

by:sg05121983
ID: 38723485
Hi Saurv,

I got below errors.

Msg 102, Level 15, State 1, Line 80
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 95
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 410
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 455
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 515
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 530
Incorrect syntax near '-'.
Warning: the floating point value '825e-2003' is too small. It will be interpreted as 0.
Warning: the floating point value '825e-2003' is too small. It will be interpreted as 0.
Warning: the floating point value '825e-2003' is too small. It will be interpreted as 0.
Msg 102, Level 15, State 1, Line 545
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 755
Incorrect syntax near '-'.
0
 

Author Comment

by:sg05121983
ID: 38723489
Msg 102, Level 15, State 1, Line 80
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 95
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 410
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 455
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 515
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 530
Incorrect syntax near '-'.
Warning: the floating point value '825e-2003' is too small. It will be interpreted as 0.
Warning: the floating point value '825e-2003' is too small. It will be interpreted as 0.
Warning: the floating point value '825e-2003' is too small. It will be interpreted as 0.
Msg 102, Level 15, State 1, Line 545
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 755
Incorrect syntax near '-'.
0
 
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 38723498
Code is running fine at my end.....

I am attaching code file with some more log inside....
try now and if you got any error post it...

Also check what you are getting in #work_to_do table..
indexes.sql
0
 

Author Comment

by:sg05121983
ID: 38723516
I got same error:

 NOTE : I am running given query on sql 2008.

please find the attached query output

Getting Details of all indexes
Msg 102, Level 15, State 1, Line 80
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 95
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 410
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 455
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 515
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 530
Incorrect syntax near '-'.
Warning: the floating point value '825e-2003' is too small. It will be interpreted as 0.
Warning: the floating point value '825e-2003' is too small. It will be interpreted as 0.
Warning: the floating point value '825e-2003' is too small. It will be interpreted as 0.
Msg 102, Level 15, State 1, Line 545
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 755
Incorrect syntax near '-'.
query-op.TIF
0
 
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 38723533
actually query is getting failed at first level.....


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')

Please run below code .. it will return multiple queries  as  output.

 execute them one by one check why they are failing at your end....

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

Open in new window

0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:sg05121983
ID: 38723539
The query is running sucessfully on sql 2008 (version - 10.50.2811.0)
got the zero rows--please find the attached output.

But it was giving error on sql 2008 (version - 10.0.5775).

It is bug?
op.TIF
0
 

Author Comment

by:sg05121983
ID: 38723570
I have executed output 1-by-1 and got 6 select statments with DB name as output (Server conatins 75 Db's).

All 5 select statments executed sucessfully. I got the eror for 6th select statment
6th select statment:

Select XYZ_TT1234567' as [DB_Name],
ps.object_id AS objectid, object_Name(PS.Object_ID,62) 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 [XYZ_1234567].sys.dm_db_index_physical_stats(62, NULL, NULL, NULL, NULL)  ps
inner join XYZ_1234567.sys.tables as tbl
    on ps.object_id = tbl.object_id
  inner join XYZ_T

The last inner join condition does not contain full DB name

Error message for above select statment:
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near XYZ_T'.
0
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38723596
Try the follwoing code

-- 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

Open in new window

0
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38723606
All you need to do is modify your work_to_do table so that it contains indexes from all databases

-----------------------------------------------------------
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	

	
	

Open in new window

0
 
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 38723652
I guess query is failing due to db name .. can you please post what you are getting in below query


select * from sys.databases


I want to check the databases name...
0
 
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 38723662
It might be a bug.....

by the way  on which version you are going to run maintenance code.... ?
0
 

Author Comment

by:sg05121983
ID: 38723675
Sure Saurv, i will share DB names.

@RohanYousf: I have executed attached script and got the below error.

Please review script once agin.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
"Date", "Page_Count", "Frag", "Command"
Msg 16924, Level 16, State 1, Line 97
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.
indexscript.sql
0
 

Author Comment

by:sg05121983
ID: 38723717
Saurv/Rohan,

Could you please review the attached script.

I got below error

"Date", "Page_Count", "Frag", "Command"
Msg 16924, Level 16, State 1, Line 97
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.
indexscript.sql
0
 
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 38723835
can you try attached script... It should work now......


I guess the issue was your db name contains single quote  (') ....

Well try now...
indexes1.sql
0
 
LVL 5

Assisted Solution

by:RehanYousaf
RehanYousaf earned 150 total points
ID: 38723839
-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;
DECLARE @database VARCHAR(50);
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]	NVARCHAR(MAX)
	,ObjectID NUMERIC
	,IndexID INT
	,PartitionNum BIGINT
	,Frag FLOAT
	,Page_Count BIGINT
)

DECLARE @Databases TABLE (
	 RowNumber BIGINT
	,Name NVARCHAR(MAX)
	,[dbID] BIGINT
)

DECLARE @x BIGINT
DECLARE @y BIGINT
DECLARE @dbID BIGINT

-----------------------------------------------------------
INSERT INTO
	@Databases
SELECT 
	 ROW_NUMBER() OVER (ORDER BY database_id) AS 'RowNumber' 
	,Name
	,database_id	
FROM
	sys.databases

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;

	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 @database, @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

Open in new window

0
 

Assisted Solution

by:sg05121983
sg05121983 earned 0 total points
ID: 38724049
Thanks Surav and RohanYousaf.

Now i'm able to run query Successfully.

Once again thanks for your efforts and help.

HATS OF TO YOUR KNOWLDGE ON CODING SKILLS :)
0
 

Author Closing Comment

by:sg05121983
ID: 38734226
--
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now