[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

sql cursor - need help

Posted on 2012-12-26
24
Medium Priority
?
418 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

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

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

656 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