Discussion :

[Scregneugneuu]

Bonjour,

J'ai eu une erreur durant un job de rebuild d'index (OFFLINE) chez un client.

Exécuté en tant qu''utilisateur*: XXXXX\SQLAgentExec.
XXXX [SQLSTATE 01000] (message 0)
XXXX [SQLSTATE 01000] (message 0)
XXXX [SQLSTATE 01000] (message 0)
XXXX [SQLSTATE 01000] (message 0)
XXXX [SQLSTATE 01000] (message 0) La base de données ' ' n'existe pas. Assurez-vous que le nom a été saisi correctement. [SQLSTATE 08004] (erreur 911). L'étape a échoué.

On peut constater qu'il a correctement fait le REBUILD pour les bases précédentes et d'un coup, il spécifie que la base ' ' n'existe pas, seulement, je n'ai aucune base de données disposant d'un nom composé d'espace.

Avez-vous déjà rencontré ce problème ? J'ai vérifié les autres jobs et aucun ne se lance en même temps.

Par avance, je vous remercie.

DEBOTTE Donovan

[aieeeuuuuu]

bonjour,

Comment fonctionne le job ? pouvez vous poster le code ?

on peut raisonnablement penser qu'il génère dynamiquement les commandes de reconstruction d'index, et qu'une erreur l’emmène à un moment à générer un code comme USE [ ] par exemple.

modifiez le code du script pour afficher/récupérer les commandes générées...

[Scregneugneuu]

Bonjour et merci pour ton commentaire
Alors, le job appelle une procédure stockée qui va bien générer dynamiquement les commandes.
Il récupère les bases :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
DECLARE dbcursor CURSOR
 FOR SELECT name FROM master.sys.databases
 WHERE state_desc = 'ONLINE'  
 and name NOT IN ('master','tempdb','model','msdb')
OPEN dbcursor  
FETCH NEXT FROM dbcursor INTO @db;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
 print @db;
 
 SELECT @edition = CONVERT(varchar(50),SERVERPROPERTY('Edition'))
 
  IF (@edition like '%Enterprise%')
  BEGIN  
 set @subquery = 'REBUILD WITH (ONLINE = ON)'
  END
  ELSE  
  BEGIN 
 set @subquery = 'REBUILD'
  END
 
 -- Recup DBID
 SET @recupDbID = N'USE ['+ @db +']; SELECT TOP 1 @dbid = DB_ID()';
 exec sp_executesql @recupDbID, N'@dbid int out', @dbid out
 
 
 SET @cmd = '
 USE ['+ @db +'];
 INSERT INTO #tempcursor
    SELECT ''USE ['+@db+']; ALTER index [''+i.name+''] ON [''+TABLE_SCHEMA+''].[''+OBJECT_NAME(ips.object_id)+''] '+@subquery+';'' as valueReturn
    FROM sys.dm_db_index_physical_stats('+@dbid+', default, default, default, default) ips
       INNER JOIN sys.indexes i
       ON ips.index_id = i.index_id AND ips.object_id = i.object_id
       INNER JOIN INFORMATION_SCHEMA.TABLES 
       ON OBJECT_NAME(ips.object_id) = TABLE_NAME
       WHERE ips.object_id > 99 AND ips.avg_fragmentation_in_percent >= 30 AND
       ips.index_id > 0 AND ips.page_count > 0;';
 EXECUTE (@cmd);

On constate bien qu'il y a deux endroits dans le job ou la commande USE XXX va être générée, mais je n'arrive pas à trouver ce qui peux causer l'erreur.
En utilisant la commande SELECT utilisé dans le CURSOR, je retrouve bien les BDD, mais aucune avec un nom rempli d'espace.

Pour ce que tu as évoqué (modifier le script), le soucis c'est que se problème survient de temps en temps seulement.

[Invité]

Peut-être qu'un PRINT (@cmd) à la place du execute permettrait de voir le code généré...

[Scregneugneuu]

Peut-être qu'un PRINT (@cmd) à la place du execute permettrait de voir le code généré...

En utilisant un PRINT (@cmd); je récupère bien les bonnes infos :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
 
TOTO
 
 USE [TOTO];
 INSERT INTO #tempcursor
    SELECT 'USE [TOTO]; ALTER index ['+i.name+'] ON ['+TABLE_SCHEMA+'].['+OBJECT_NAME(ips.object_id)+'] REBUILD;' as valueReturn
    FROM sys.dm_db_index_physical_stats(5, default, default, default, default) ips
       INNER JOIN sys.indexes i
       ON ips.index_id = i.index_id AND ips.object_id = i.object_id
       INNER JOIN INFORMATION_SCHEMA.TABLES 
       ON OBJECT_NAME(ips.object_id) = TABLE_NAME
       WHERE ips.object_id > 99 AND ips.avg_fragmentation_in_percent >= 30 AND
       ips.index_id > 0 AND ips.page_count > 0;
TATA
 
 USE [TATA];
 INSERT INTO #tempcursor
    SELECT 'USE [TATA]; ALTER index ['+i.name+'] ON ['+TABLE_SCHEMA+'].['+OBJECT_NAME(ips.object_id)+'] REBUILD;' as valueReturn
    FROM sys.dm_db_index_physical_stats(6, default, default, default, default) ips
       INNER JOIN sys.indexes i
       ON ips.index_id = i.index_id AND ips.object_id = i.object_id
       INNER JOIN INFORMATION_SCHEMA.TABLES 
       ON OBJECT_NAME(ips.object_id) = TABLE_NAME
       WHERE ips.object_id > 99 AND ips.avg_fragmentation_in_percent >= 30 AND
       ips.index_id > 0 AND ips.page_count > 0;
TITI
 
 USE [TITI];
 INSERT INTO #tempcursor
    SELECT 'USE [TITI]; ALTER index ['+i.name+'] ON ['+TABLE_SCHEMA+'].['+OBJECT_NAME(ips.object_id)+'] REBUILD;' as valueReturn
    FROM sys.dm_db_index_physical_stats(8, default, default, default, default) ips
       INNER JOIN sys.indexes i
       ON ips.index_id = i.index_id AND ips.object_id = i.object_id
       INNER JOIN INFORMATION_SCHEMA.TABLES 
       ON OBJECT_NAME(ips.object_id) = TABLE_NAME
       WHERE ips.object_id > 99 AND ips.avg_fragmentation_in_percent >= 30 AND
       ips.index_id > 0 AND ips.page_count > 0;

[aieeeuuuuu]

quel est le contenu de #tempcursor après l’exécution du script et... quelle est la suite du script ?

[Scregneugneuu]

Bonjour,

Voici le code du script :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
 
DECLARE
@db nvarchar(255),
@cmd NVARCHAR(2000),
@recupDbID NVARCHAR(2000),
@dbid NVARCHAR(2000),
@query nvarchar(max),
@edition nvarchar(max),
@subquery nvarchar(max);
BEGIN
 
create table #tempcursor (query nvarchar(255));
 
DECLARE dbcursor CURSOR
 FOR SELECT name FROM master.sys.databases
 WHERE state_desc = 'ONLINE'  
 and name NOT IN ('master','tempdb','model','msdb')
OPEN dbcursor  
FETCH NEXT FROM dbcursor INTO @db;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
 print @db;
 
 SELECT @edition = CONVERT(varchar(50),SERVERPROPERTY('Edition'))
 
  IF (@edition like '%Enterprise%')
  BEGIN  
 set @subquery = 'REBUILD WITH (ONLINE = ON)'
  END
  ELSE  
  BEGIN 
 set @subquery = 'REBUILD'
  END
 
 -- Recup DBID
 SET @recupDbID = N'USE ['+ @db +']; SELECT TOP 1 @dbid = DB_ID()';
 exec sp_executesql @recupDbID, N'@dbid int out', @dbid out
 
 
 SET @cmd = '
 USE ['+ @db +'];
 INSERT INTO #tempcursor
    SELECT ''USE ['+@db+']; ALTER index [''+i.name+''] ON [''+TABLE_SCHEMA+''].[''+OBJECT_NAME(ips.object_id)+''] '+@subquery+';'' as valueReturn
    FROM sys.dm_db_index_physical_stats('+@dbid+', default, default, default, default) ips
       INNER JOIN sys.indexes i
       ON ips.index_id = i.index_id AND ips.object_id = i.object_id
       INNER JOIN INFORMATION_SCHEMA.TABLES 
       ON OBJECT_NAME(ips.object_id) = TABLE_NAME
       WHERE ips.object_id > 99 AND ips.avg_fragmentation_in_percent >= 30 AND
       ips.index_id > 0 AND ips.page_count > 1000;';
 EXECUTE (@cmd);
 --PRINT (@cmd);
 FETCH NEXT FROM dbcursor INTO @db;
 end
 close dbcursor;
 deallocate dbcursor;
 
 DECLARE idxCursor CURSOR
 FOR SELECT * FROM #tempcursor
 
OPEN idxCursor  
FETCH NEXT FROM idxCursor INTO @db;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
BEGIN TRY
 print @db;
 exec (@db);
END TRY
    BEGIN CATCH 
     PRINT 'ERROR : ' +  @db;
    END CATCH
FETCH NEXT FROM idxCursor INTO @db;
end
 
close idxCursor;
deallocate idxCursor;
 
drop table #tempcursor;
 
END

[SQLpro]

Une récriture possible (début :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
DECLARE @db sysname, @subquery NVARCHAR(32), @cmd NVARCHAR(max) = N''
 
DECLARE dbcursor CURSOR
FOR 
   SELECT name 
   FROM   master.sys.databases
   WHERE  state_desc = 'ONLINE'  
     AND  database_id > 4
	 AND  source_database_id IS NULL
	 AND  is_read_only = 0
--> il faudrait rajouter que ce n'est pas un réplica alwaysOn...
--> pour cela voir l'article que j'ai écrit : 
--> http://mssqlserver.fr/agent-sql-et-replicas-alwayson-hadr-availability-groups-ma-base-est-elle-active/
OPEN dbcursor;  
FETCH NEXT FROM dbcursor INTO @db;
WHILE @@FETCH_STATUS = 0
BEGIN
   print @db;
 
   IF CAST(SERVERPROPERTY('Edition') AS NVARCHAR(1024)) LIKE 'Enterprise %'
      SET @subquery = ' WITH (ONLINE = ON)'
   ELSE SET @subquery = '';
 
   SET @cmd = 'SELECT N''ALTER index [''+i.name+''] ON [' + @db + '].[''+s.name+''].[''+o.name+''] REBUILD '+@subquery+';''
               FROM   sys.dm_db_index_physical_stats(DB_ID('''+@db+'''), default, default, default, default) ips
                      INNER JOIN [' + @db + '].sys.indexes i
                         ON ips.index_id = i.index_id AND ips.object_id = i.object_id
                      INNER JOIN [' + @db + '].sys.objects AS o
		    			   ON i.object_id = o.object_id
                      INNER JOIN [' + @db + '].sys.schemas AS s
				    	   ON o.schema_id = s.schema_id
               WHERE  ips.avg_fragmentation_in_percent >= 30 
			     AND  ips.index_id > 0 
			     AND  ips.page_count > 64;';
   PRINT @cmd; 
   FETCH NEXT FROM dbcursor INTO @db;
END;
-- EXECUTE (@cmd);
 
CLOSE dbcursor
DEALLOCATE dbcursor

A +

A me lire :

[aieeeuuuuu]

quelle est l'instruction qui précède l'erreur ?

Vous devez avoir un caractère spécial (comme un "]") dans le nom d'un des objets, qui mets le brin.


Essayez en utilisant QUOTENAME plutôt que mettre "à la main" les noms d'objets entre crochets : QUOTENAME gérera les noms d'objets farfelus

[Scregneugneuu]

Je ne connais pas "QUOTENAME" je ne vois pas ou l'inclure du coup :/ ==> je suis allé consulter rapidement la doc SQL Server
Les noms des bases sont récupérées par le script :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
create table #tempcursor (query nvarchar(255));
 
DECLARE dbcursor CURSOR
 FOR SELECT name FROM master.sys.databases
 WHERE state_desc = 'ONLINE'  
 and name NOT IN ('master','tempdb','model','msdb')

Ah moins que ça soit quelque chose comme ça que tu évoquais ? :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SET @recupDbID = N'USE QUOTENAME(' @db '); SELECT TOP 1 @dbid = DB_ID()';

Merci @SQLpro pour le script, je verrais pour l'essayer sur un serveur de test.

[aieeeuuuuu]

en l'occurence, ce serait plutôt :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
SET @recupDbID = N'USE ' + QUOTENAME(@db) +  '; SELECT TOP 1 @dbid = DB_ID()';

a appliquer sur tous les noms d'objet dans le script : BDD, schéma, table, index,...

[Invité]

on se demande un peu la pertinence du TOP 1 dans

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT TOP 1 @dbid = DB_ID()';

Et pourquoi se compliquer à récupérer l'id de la bd alors qu'on l'a dans la première table questionnée master.sys.databases ?

[Scregneugneuu]

a appliquer sur tous les noms d'objet dans le script : BDD, schéma, table, index,...

Du coup j'ai fais quelques modifications afin d'inclure le QUOTENAME :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
-- Recup DBID
-- SET @recupDbID = N'USE ['+ @db +']; SELECT TOP 1 @dbid = DB_ID()';
 SET @recupDbID = N'USE '+ QUOTENAME(@db) +'; SELECT TOP 1 @dbid = DB_ID()';
 exec sp_executesql @recupDbID, N'@dbid int out', @dbid out
 
 
 SET @cmd = '
 USE '+ QUOTENAME(@db)+ ';
 INSERT INTO #tempcursor
    SELECT ''USE '+QUOTENAME(@db)+'; ALTER index [''+i.name+''] ON [''+TABLE_SCHEMA+''].[''+OBJECT_NAME(ips.object_id)+''] '+@subquery+';'' as valueReturn
    FROM sys.dm_db_index_physical_stats('+@dbid+', default, default, default, default) ips
       INNER JOIN sys.indexes i
       ON ips.index_id = i.index_id AND ips.object_id = i.object_id
       INNER JOIN INFORMATION_SCHEMA.TABLES 
       ON OBJECT_NAME(ips.object_id) = TABLE_NAME
       WHERE ips.object_id > 99 AND ips.avg_fragmentation_in_percent >= 30 AND
       ips.index_id > 0 AND ips.page_count > 1000;';

J'ai de nouveau eu le soucis, j'ai donc en plus du QUOTENAME, ajouté un retry 2 avec 15 minutes d'intervalles. Je vais voir si le soucis se reproduit désormais, je vous tiendrez au courant

[Scregneugneuu]

Merci à vous pour votre aide, j'ai pu tester avec les QUOTENAME et je n'ai pas eu de problème.
Le vrai test sera Dimanche à 11h00, mais ca devrait le faire

Encore merci pour votre aide, je passe en résolu

[Scregneugneuu]

Bonjour

Alors, malgré les QUOTENAME(@db), j'ai eu de nouveau le soucis, j'ai mis un retry sur le job et ils fonctionnent toujours désormais, mais je préférerais quand même corriger le problème directement dans le script.
J'ai rajouté au début "AND name NOT LIKE (' %');" donc il ne devrait pas s'occuper des bases ayant un nom commençant par un espace :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
 
DECLARE dbcursor CURSOR
 FOR SELECT name FROM master.sys.databases
 WHERE state_desc = 'ONLINE'  
 and name NOT IN ('master','tempdb','model','msdb') AND name NOT LIKE (' %');

Je vais voir si cela corrige le soucis.

[aieeeuuuuu]

bonjour,

Comme suggéré par 7gyY9w1ZY6ySRgPeaefZ, il faudrait que vous récupériez les commandes générées.
Sans quoi... vous pourrez chercher longtemps d'où vient le problème !

[Scregneugneuu]

Voici les requêtes qui sont générées sur mon serveur perso :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
AdventureWorks2017 [SQLSTATE 01000]
USE [AdventureWorks2017];   INSERT INTO #tempcursor      SELECT 'USE [AdventureWorks2017]; ALTER index ['+i.name+'] ON ['+TABLE_SCHEMA+'].['+OBJECT_NAME(ips.object_id)+'] REBUILD;' as valueReturn      FROM sys.dm_db_index_physical_stats(5, default, default, default, default) ips         INNER JOIN sys.indexes i         ON ips.index_id = i.index_id AND ips.object_id = i.object_id         INNER JOIN INFORMATION_SCHEMA.TABLES          ON OBJECT_NAME(ips.object_id) = TABLE_NAME         WHERE ips.object_id > 99 AND ips.avg_fragmentation_in_percent >= 30 AND         ips.index_id > 0 AND ips.page_count > 1000; [SQLSTATE 01000]
 
AdventureWorks2017_2 [SQLSTATE 01000]
USE [AdventureWorks2017_2];   INSERT INTO #tempcursor      SELECT 'USE [AdventureWorks2017_2]; ALTER index ['+i.name+'] ON ['+TABLE_SCHEMA+'].['+OBJECT_NAME(ips.object_id)+'] REBUILD;' as valueReturn      FROM sys.dm_db_index_physical_stats(6, default, default, default, default) ips         INNER JOIN sys.indexes i         ON ips.index_id = i.index_id AND ips.object_id = i.object_id         INNER JOIN INFORMATION_SCHEMA.TABLES          ON OBJECT_NAME(ips.object_id) = TABLE_NAME         WHERE ips.object_id > 99 AND ips.avg_fragmentation_in_percent >= 30 AND         ips.index_id > 0 AND ips.page_count > 1000; [SQLSTATE 01000]
 
AdventureWorks [SQLSTATE 01000]
USE [AdventureWorks];   INSERT INTO #tempcursor      SELECT 'USE [AdventureWorks]; ALTER index ['+i.name+'] ON ['+TABLE_SCHEMA+'].['+OBJECT_NAME(ips.object_id)+'] REBUILD;' as valueReturn      FROM sys.dm_db_index_physical_stats(9, default, default, default, default) ips         INNER JOIN sys.indexes i         ON ips.index_id = i.index_id AND ips.object_id = i.object_id         INNER JOIN INFORMATION_SCHEMA.TABLES          ON OBJECT_NAME(ips.object_id) = TABLE_NAME         WHERE ips.object_id > 99 AND ips.avg_fragmentation_in_percent >= 30 AND         ips.index_id > 0 AND ips.page_count > 1000; [SQLSTATE 01000]
 
AdventureWorks2016 [SQLSTATE 01000]
USE [AdventureWorks2016];   INSERT INTO #tempcursor      SELECT 'USE [AdventureWorks2016]; ALTER index ['+i.name+'] ON ['+TABLE_SCHEMA+'].['+OBJECT_NAME(ips.object_id)+'] REBUILD;' as valueReturn      FROM sys.dm_db_index_physical_stats(10, default, default, default, default) ips         INNER JOIN sys.indexes i         ON ips.index_id = i.index_id AND ips.object_id = i.object_id         INNER JOIN INFORMATION_SCHEMA.TABLES          ON OBJECT_NAME(ips.object_id) = TABLE_NAME         WHERE ips.object_id > 99 AND ips.avg_fragmentation_in_percent >= 30 AND         ips.index_id > 0 AND ips.page_count > 1000; [SQLSTATE 01000]
 
TSQL [SQLSTATE 01000]
USE [TSQL];   INSERT INTO #tempcursor      SELECT 'USE [TSQL]; ALTER index ['+i.name+'] ON ['+TABLE_SCHEMA+'].['+OBJECT_NAME(ips.object_id)+'] REBUILD;' as valueReturn      FROM sys.dm_db_index_physical_stats(11, default, default, default, default) ips         INNER JOIN sys.indexes i         ON ips.index_id = i.index_id AND ips.object_id = i.object_id         INNER JOIN INFORMATION_SCHEMA.TABLES          ON OBJECT_NAME(ips.object_id) = TABLE_NAME         WHERE ips.object_id > 99 AND ips.avg_fragmentation_in_percent >= 30 AND         ips.index_id > 0 AND ips.page_count > 1000; [SQLSTATE 01000]

Et voici les commandes générées pour la REORG et le REBUILD sur le serveur du client (ne pas prendre en compte les valeurs de seuils élevées) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
Travail «*reorganize_indexes_all_db*»*: étape 1, «*JOB_STEP1*»*: début d'exécution de 2020-04-08 11:12:54
 
TOTO [SQLSTATE 01000]
USE [TOTO];   INSERT INTO #tempcursor  	   SELECT 'USE [TOTO]; ALTER index ['+i.name+'] ON ['+TABLE_SCHEMA+'].['+OBJECT_NAME(ips.object_id)+'] REORGANIZE WITH (LOB_COMPACTION = ON);' as valueReturn  	   FROM sys.dm_db_index_physical_stats(5, default, default, default, default) ips         INNER JOIN sys.indexes i         ON ips.index_id = i.index_id AND ips.object_id = i.object_id         INNER JOIN INFORMATION_SCHEMA.TABLES          ON OBJECT_NAME(ips.object_id) = TABLE_NAME         WHERE ips.object_id > 99 AND ips.avg_fragmentation_in_percent BETWEEN 30 AND 60 AND         ips.index_id > 0 AND ips.page_count > 1000; [SQLSTATE 01000]
 
TITI [SQLSTATE 01000]
USE [TITI];   INSERT INTO #tempcursor  	   SELECT 'USE [TITI]; ALTER index ['+i.name+'] ON ['+TABLE_SCHEMA+'].['+OBJECT_NAME(ips.object_id)+'] REORGANIZE WITH (LOB_COMPACTION = ON);' as valueReturn  	   FROM sys.dm_db_index_physical_stats(6, default, default, default, default) ips         INNER JOIN sys.indexes i         ON ips.index_id = i.index_id AND ips.object_id = i.object_id         INNER JOIN INFORMATION_SCHEMA.TABLES          ON OBJECT_NAME(ips.object_id) = TABLE_NAME         WHERE ips.object_id > 99 AND ips.avg_fragmentation_in_percent BETWEEN 30 AND 60 AND         ips.index_id > 0 AND ips.page_count > 1000; [SQLSTATE 01000]
 
TATA [SQLSTATE 01000]
USE [TATA];   INSERT INTO #tempcursor  	   SELECT 'USE [TATA]; ALTER index ['+i.name+'] ON ['+TABLE_SCHEMA+'].['+OBJECT_NAME(ips.object_id)+'] REORGANIZE WITH (LOB_COMPACTION = ON);' as valueReturn  	   FROM sys.dm_db_index_physical_stats(8, default, default, default, default) ips         INNER JOIN sys.indexes i         ON ips.index_id = i.index_id AND ips.object_id = i.object_id         INNER JOIN INFORMATION_SCHEMA.TABLES          ON OBJECT_NAME(ips.object_id) = TABLE_NAME         WHERE ips.object_id > 99 AND ips.avg_fragmentation_in_percent BETWEEN 30 AND 60 AND         ips.index_id > 0 AND ips.page_count > 1000; [SQLSTATE 01000]
 
TETE [SQLSTATE 01000]
USE [TETE];   INSERT INTO #tempcursor  	   SELECT 'USE [TETE]; ALTER index ['+i.name+'] ON ['+TABLE_SCHEMA+'].['+OBJECT_NAME(ips.object_id)+'] REORGANIZE WITH (LOB_COMPACTION = ON);' as valueReturn  	   FROM sys.dm_db_index_physical_stats(9, default, default, default, default) ips         INNER JOIN sys.indexes i         ON ips.index_id = i.index_id AND ips.object_id = i.object_id         INNER JOIN INFORMATION_SCHEMA.TABLES          ON OBJECT_NAME(ips.object_id) = TABLE_NAME         WHERE ips.object_id > 99 AND ips.avg_fragmentation_in_percent BETWEEN 30 AND 60 AND         ips.index_id > 0 AND ips.page_count > 1000; [SQLSTATE 01000]
 
TUTU [SQLSTATE 01000]
USE [TUTU];   INSERT INTO #tempcursor  	   SELECT 'USE [TUTU]; ALTER index ['+i.name+'] ON ['+TABLE_SCHEMA+'].['+OBJECT_NAME(ips.object_id)+'] REORGANIZE WITH (LOB_COMPACTION = ON);' as valueReturn  	   FROM sys.dm_db_index_physical_stats(10, default, default, default, default) ips         INNER JOIN sys.indexes i         ON ips.index_id = i.index_id AND ips.object_id = i.object_id         INNER JOIN INFORMATION_SCHEMA.TABLES          ON OBJECT_NAME(ips.object_id) = TABLE_NAME         WHERE ips.object_id > 99 AND ips.avg_fragmentation_in_percent BETWEEN 30 AND 60 AND         ips.index_id > 0 AND ips.page_count > 1000; [SQLSTATE 01000]
 
ATAT [SQLSTATE 01000]
USE [ATAT];   INSERT INTO #tempcursor  	   SELECT 'USE [ATAT]; ALTER index ['+i.name+'] ON ['+TABLE_SCHEMA+'].['+OBJECT_NAME(ips.object_id)+'] REORGANIZE WITH (LOB_COMPACTION = ON);' as valueReturn  	   FROM sys.dm_db_index_physical_stats(11, default, default, default, default) ips         INNER JOIN sys.indexes i         ON ips.index_id = i.index_id AND ips.object_id = i.object_id         INNER JOIN INFORMATION_SCHEMA.TABLES          ON OBJECT_NAME(ips.object_id) = TABLE_NAME         WHERE ips.object_id > 99 AND ips.avg_fragmentation_in_percent BETWEEN 30 AND 60 AND         ips.index_id > 0 AND ips.page_count > 1000; [SQLSTATE 01000]
 
ITIT [SQLSTATE 01000]
USE [ITIT];   INSERT INTO #tempcursor  	   SELECT 'USE [ITIT]; ALTER index ['+i.name+'] ON ['+TABLE_SCHEMA+'].['+OBJECT_NAME(ips.object_id)+'] REORGANIZE WITH (LOB_COMPACTION = ON);' as valueReturn  	   FROM sys.dm_db_index_physical_stats(12, default, default, default, default) ips         INNER JOIN sys.indexes i         ON ips.index_id = i.index_id AND ips.object_id = i.object_id         INNER JOIN INFORMATION_SCHEMA.TABLES          ON OBJECT_NAME(ips.object_id) = TABLE_NAME         WHERE ips.object_id > 99 AND ips.avg_fragmentation_in_percent BETWEEN 30 AND 60 AND         ips.index_id > 0 AND ips.page_count > 1000; [SQLSTATE 01000]
 
ETET [SQLSTATE 01000]
USE [ETET];   INSERT INTO #tempcursor  	   SELECT 'USE [ETET]; ALTER index ['+i.name+'] ON ['+TABLE_SCHEMA+'].['+OBJECT_NAME(ips.object_id)+'] REORGANIZE WITH (LOB_COMPACTION = ON);' as valueReturn  	   FROM sys.dm_db_index_physical_stats(15, default, default, default, default) ips         INNER JOIN sys.indexes i         ON ips.index_id = i.index_id AND ips.object_id = i.object_id         INNER JOIN INFORMATION_SCHEMA.TABLES          ON OBJECT_NAME(ips.object_id) = TABLE_NAME         WHERE ips.object_id > 99 AND ips.avg_fragmentation_in_percent BETWEEN 30 AND 60 AND         ips.index_id > 0 AND ips.page_count > 1000; [SQLSTATE 01000]
 
OTOT [SQLSTATE 01000]
USE [OTOT];   INSERT INTO #tempcursor  	   SELECT 'USE [OTOT]; ALTER index ['+i.name+'] ON ['+TABLE_SCHEMA+'].['+OBJECT_NAME(ips.object_id)+'] REORGANIZE WITH (LOB_COMPACTION = ON);' as valueReturn  	   FROM sys.dm_db_index_physical_stats(16, default, default, default, default) ips         INNER JOIN sys.indexes i         ON ips.index_id = i.index_id AND ips.object_id = i.object_id         INNER JOIN INFORMATION_SCHEMA.TABLES          ON OBJECT_NAME(ips.object_id) = TABLE_NAME         WHERE ips.object_id > 99 AND ips.avg_fragmentation_in_percent BETWEEN 30 AND 60 AND         ips.index_id > 0 AND ips.page_count > 1000; [SQLSTATE 01000]
 
IKIK [SQLSTATE 01000]
USE [IKIK];   INSERT INTO #tempcursor  	   SELECT 'USE [IKIK]; ALTER index ['+i.name+'] ON ['+TABLE_SCHEMA+'].['+OBJECT_NAME(ips.object_id)+'] REORGANIZE WITH (LOB_COMPACTION = ON);' as valueReturn  	   FROM sys.dm_db_index_physical_stats(17, default, default, default, default) ips         INNER JOIN sys.indexes i         ON ips.index_id = i.index_id AND ips.object_id = i.object_id         INNER JOIN INFORMATION_SCHEMA.TABLES          ON OBJECT_NAME(ips.object_id) = TABLE_NAME         WHERE ips.object_id > 99 AND ips.avg_fragmentation_in_percent BETWEEN 30 AND 60 AND         ips.index_id > 0 AND ips.page_count > 1000; [SQLSTATE 01000]
 
OKOK [SQLSTATE 01000]
USE [OKOK];   INSERT INTO #tempcursor  	   SELECT 'USE [OKOK]; ALTER index ['+i.name+'] ON ['+TABLE_SCHEMA+'].['+OBJECT_NAME(ips.object_id)+'] REORGANIZE WITH (LOB_COMPACTION = ON);' as valueReturn  	   FROM sys.dm_db_index_physical_stats(19, default, default, default, default) ips         INNER JOIN sys.indexes i         ON ips.index_id = i.index_id AND ips.object_id = i.object_id         INNER JOIN INFORMATION_SCHEMA.TABLES          ON OBJECT_NAME(ips.object_id) = TABLE_NAME         WHERE ips.object_id > 99 AND ips.avg_fragmentation_in_percent BETWEEN 30 AND 60 AND         ips.index_id > 0 AND ips.page_count > 1000; [SQLSTATE 01000]
 
EKEK [SQLSTATE 01000]
USE [EKEK];   INSERT INTO #tempcursor  	   SELECT 'USE [EKEK]; ALTER index ['+i.name+'] ON ['+TABLE_SCHEMA+'].['+OBJECT_NAME(ips.object_id)+'] REORGANIZE WITH (LOB_COMPACTION = ON);' as valueReturn  	   FROM sys.dm_db_index_physical_stats(20, default, default, default, default) ips         INNER JOIN sys.indexes i         ON ips.index_id = i.index_id AND ips.object_id = i.object_id         INNER JOIN INFORMATION_SCHEMA.TABLES          ON OBJECT_NAME(ips.object_id) = TABLE_NAME         WHERE ips.object_id > 99 AND ips.avg_fragmentation_in_percent BETWEEN 30 AND 60 AND         ips.index_id > 0 AND ips.page_count > 1000; [SQLSTATE 01000]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
Travail « rebuild_indexes_all_db » : étape 1, « JOB_STEP1 » : début d'exécution de 2020-04-08 11:12:54
 
IKIK [SQLSTATE 01000]
USE [IKIK];   INSERT INTO #tempcursor      SELECT 'USE [IKIK]; ALTER index ['+i.name+'] ON ['+TABLE_SCHEMA+'].['+OBJECT_NAME(ips.object_id)+'] REBUILD;' as valueReturn      FROM sys.dm_db_index_physical_stats(5, default, default, default, default) ips         INNER JOIN sys.indexes i         ON ips.index_id = i.index_id AND ips.object_id = i.object_id         INNER JOIN INFORMATION_SCHEMA.TABLES          ON OBJECT_NAME(ips.object_id) = TABLE_NAME         WHERE ips.object_id > 99 AND ips.avg_fragmentation_in_percent >= 60 AND         ips.index_id > 0 AND ips.page_count > 1000; [SQLSTATE 01000]
 
OKOK [SQLSTATE 01000]
USE [OKOK];   INSERT INTO #tempcursor      SELECT 'USE [OKOK]; ALTER index ['+i.name+'] ON ['+TABLE_SCHEMA+'].['+OBJECT_NAME(ips.object_id)+'] REBUILD;' as valueReturn      FROM sys.dm_db_index_physical_stats(6, default, default, default, default) ips         INNER JOIN sys.indexes i         ON ips.index_id = i.index_id AND ips.object_id = i.object_id         INNER JOIN INFORMATION_SCHEMA.TABLES          ON OBJECT_NAME(ips.object_id) = TABLE_NAME         WHERE ips.object_id > 99 AND ips.avg_fragmentation_in_percent >= 60 AND         ips.index_id > 0 AND ips.page_count > 1000; [SQLSTATE 01000]
 
AKAK [SQLSTATE 01000]
USE [AKAK];   INSERT INTO #tempcursor      SELECT 'USE [AKAK]; ALTER index ['+i.name+'] ON ['+TABLE_SCHEMA+'].['+OBJECT_NAME(ips.object_id)+'] REBUILD;' as valueReturn      FROM sys.dm_db_index_physical_stats(8, default, default, default, default) ips         INNER JOIN sys.indexes i         ON ips.index_id = i.index_id AND ips.object_id = i.object_id         INNER JOIN INFORMATION_SCHEMA.TABLES          ON OBJECT_NAME(ips.object_id) = TABLE_NAME         WHERE ips.object_id > 99 AND ips.avg_fragmentation_in_percent >= 60 AND         ips.index_id > 0 AND ips.page_count > 1000; [SQLSTATE 01000]
 
TITI [SQLSTATE 01000]
USE [TITI];   INSERT INTO #tempcursor      SELECT 'USE [TITI]; ALTER index ['+i.name+'] ON ['+TABLE_SCHEMA+'].['+OBJECT_NAME(ips.object_id)+'] REBUILD;' as valueReturn      FROM sys.dm_db_index_physical_stats(9, default, default, default, default) ips         INNER JOIN sys.indexes i         ON ips.index_id = i.index_id AND ips.object_id = i.object_id         INNER JOIN INFORMATION_SCHEMA.TABLES          ON OBJECT_NAME(ips.object_id) = TABLE_NAME         WHERE ips.object_id > 99 AND ips.avg_fragmentation_in_percent >= 60 AND         ips.index_id > 0 AND ips.page_count > 1000; [SQLSTATE 01000]
 
TOTO [SQLSTATE 01000]
USE [TOTO];   INSERT INTO #tempcursor      SELECT 'USE [TOTO]; ALTER index ['+i.name+'] ON ['+TABLE_SCHEMA+'].['+OBJECT_NAME(ips.object_id)+'] REBUILD;' as valueReturn      FROM sys.dm_db_index_physical_stats(10, default, default, default, default) ips         INNER JOIN sys.indexes i         ON ips.index_id = i.index_id AND ips.object_id = i.object_id         INNER JOIN INFORMATION_SCHEMA.TABLES          ON OBJECT_NAME(ips.object_id) = TABLE_NAME         WHERE ips.object_id > 99 AND ips.avg_fragmentation_in_percent >= 60 AND         ips.index_id > 0 AND ips.page_count > 1000; [SQLSTATE 01000]
 
TATA [SQLSTATE 01000]
USE [TATA];   INSERT INTO #tempcursor      SELECT 'USE [TATA]; ALTER index ['+i.name+'] ON ['+TABLE_SCHEMA+'].['+OBJECT_NAME(ips.object_id)+'] REBUILD;' as valueReturn      FROM sys.dm_db_index_physical_stats(11, default, default, default, default) ips         INNER JOIN sys.indexes i         ON ips.index_id = i.index_id AND ips.object_id = i.object_id         INNER JOIN INFORMATION_SCHEMA.TABLES          ON OBJECT_NAME(ips.object_id) = TABLE_NAME         WHERE ips.object_id > 99 AND ips.avg_fragmentation_in_percent >= 60 AND         ips.index_id > 0 AND ips.page_count > 1000; [SQLSTATE 01000]
 
TETE [SQLSTATE 01000]
USE [TETE];   INSERT INTO #tempcursor      SELECT 'USE [TETE]; ALTER index ['+i.name+'] ON ['+TABLE_SCHEMA+'].['+OBJECT_NAME(ips.object_id)+'] REBUILD;' as valueReturn      FROM sys.dm_db_index_physical_stats(12, default, default, default, default) ips         INNER JOIN sys.indexes i         ON ips.index_id = i.index_id AND ips.object_id = i.object_id         INNER JOIN INFORMATION_SCHEMA.TABLES          ON OBJECT_NAME(ips.object_id) = TABLE_NAME         WHERE ips.object_id > 99 AND ips.avg_fragmentation_in_percent >= 60 AND         ips.index_id > 0 AND ips.page_count > 1000; [SQLSTATE 01000]
 
UKUK [SQLSTATE 01000]
USE [UKUK];   INSERT INTO #tempcursor      SELECT 'USE [UKUK]; ALTER index ['+i.name+'] ON ['+TABLE_SCHEMA+'].['+OBJECT_NAME(ips.object_id)+'] REBUILD;' as valueReturn      FROM sys.dm_db_index_physical_stats(15, default, default, default, default) ips         INNER JOIN sys.indexes i         ON ips.index_id = i.index_id AND ips.object_id = i.object_id         INNER JOIN INFORMATION_SCHEMA.TABLES          ON OBJECT_NAME(ips.object_id) = TABLE_NAME         WHERE ips.object_id > 99 AND ips.avg_fragmentation_in_percent >= 60 AND         ips.index_id > 0 AND ips.page_count > 1000; [SQLSTATE 01000]
 
OTOT [SQLSTATE 01000]
USE [OTOT];   INSERT INTO #tempcursor      SELECT 'USE [OTOT]; ALTER index ['+i.name+'] ON ['+TABLE_SCHEMA+'].['+OBJECT_NAME(ips.object_id)+'] REBUILD;' as valueReturn      FROM sys.dm_db_index_physical_stats(16, default, default, default, default) ips         INNER JOIN sys.indexes i         ON ips.index_id = i.index_id AND ips.object_id = i.object_id         INNER JOIN INFORMATION_SCHEMA.TABLES          ON OBJECT_NAME(ips.object_id) = TABLE_NAME         WHERE ips.object_id > 99 AND ips.avg_fragmentation_in_percent >= 60 AND         ips.index_id > 0 AND ips.page_count > 1000; [SQLSTATE 01000]
 
EKEK [SQLSTATE 01000]
USE [EKEK];   INSERT INTO #tempcursor      SELECT 'USE [EKEK]; ALTER index ['+i.name+'] ON ['+TABLE_SCHEMA+'].['+OBJECT_NAME(ips.object_id)+'] REBUILD;' as valueReturn      FROM sys.dm_db_index_physical_stats(17, default, default, default, default) ips         INNER JOIN sys.indexes i         ON ips.index_id = i.index_id AND ips.object_id = i.object_id         INNER JOIN INFORMATION_SCHEMA.TABLES          ON OBJECT_NAME(ips.object_id) = TABLE_NAME         WHERE ips.object_id > 99 AND ips.avg_fragmentation_in_percent >= 60 AND         ips.index_id > 0 AND ips.page_count > 1000; [SQLSTATE 01000]
 
ITIT [SQLSTATE 01000]
USE [ITIT];   INSERT INTO #tempcursor      SELECT 'USE [ITIT]; ALTER index ['+i.name+'] ON ['+TABLE_SCHEMA+'].['+OBJECT_NAME(ips.object_id)+'] REBUILD;' as valueReturn      FROM sys.dm_db_index_physical_stats(19, default, default, default, default) ips         INNER JOIN sys.indexes i         ON ips.index_id = i.index_id AND ips.object_id = i.object_id         INNER JOIN INFORMATION_SCHEMA.TABLES          ON OBJECT_NAME(ips.object_id) = TABLE_NAME         WHERE ips.object_id > 99 AND ips.avg_fragmentation_in_percent >= 60 AND         ips.index_id > 0 AND ips.page_count > 1000; [SQLSTATE 01000]
 
ETET [SQLSTATE 01000]
USE [ETET];   INSERT INTO #tempcursor      SELECT 'USE [ETET]; ALTER index ['+i.name+'] ON ['+TABLE_SCHEMA+'].['+OBJECT_NAME(ips.object_id)+'] REBUILD;' as valueReturn      FROM sys.dm_db_index_physical_stats(20, default, default, default, default) ips         INNER JOIN sys.indexes i         ON ips.index_id = i.index_id AND ips.object_id = i.object_id         INNER JOIN INFORMATION_SCHEMA.TABLES          ON OBJECT_NAME(ips.object_id) = TABLE_NAME         WHERE ips.object_id > 99 AND ips.avg_fragmentation_in_percent >= 60 AND         ips.index_id > 0 AND ips.page_count > 1000; [SQLSTATE 01000]

Et voici l'erreur rencontrée sur le serveur du client :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
Message
Exécuté en tant qu''utilisateur*: NT SERVICE\SQLSERVERAGENT. EKEK [SQLSTATE 01000] (message 0)  OKOK [SQLSTATE 01000] (message 0)  IKIK [SQLSTATE 01000] (message 0)  TOTO [SQLSTATE 01000] (message 0)  La base de données '                                                                                                                                ' n'existe pas. Assurez-vous que le nom a été saisi correctement. [SQLSTATE 08004] (erreur 911).  L'étape a échoué.

J'ai don appliqué le QUOTENAME ainsi que le "AND name NOT LIKE (' %');" sur ce serveur également afin de voir si ça permet de corriger le problème :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
 
USE [TOTO]
GO
/****** Object:  StoredProcedure [dbo].[rebuild_indexes_all_db]    Script Date: 08/04/2020 11:29:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[rebuild_indexes_all_db]
AS
 
DECLARE
@db nvarchar(255),
@cmd NVARCHAR(2000),
@recupDbID NVARCHAR(2000),
@dbid NVARCHAR(2000),
@query nvarchar(max),
@edition nvarchar(max),
@subquery nvarchar(max);
BEGIN
 
create table #tempcursor (query nvarchar(255));
 
DECLARE dbcursor CURSOR
 FOR SELECT name FROM master.sys.databases
 WHERE state_desc = 'ONLINE'  
 and name NOT IN ('master','tempdb','model','msdb','distribution') AND name NOT LIKE ('DEV_%') AND name NOT LIKE (' %');
OPEN dbcursor  
FETCH NEXT FROM dbcursor INTO @db;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
 print @db;
 
 SELECT @edition = CONVERT(varchar(50),SERVERPROPERTY('Edition'))
 
  IF (@edition like '%Enterprise%')
  BEGIN  
 set @subquery = 'REBUILD WITH (ONLINE = ON)'
  END
  ELSE  
  BEGIN 
 set @subquery = 'REBUILD'
  END
 
 -- Recup DBID
 SET @recupDbID = N'USE '+ QUOTENAME(@db) +'; SELECT TOP 1 @dbid = DB_ID()';
 exec sp_executesql @recupDbID, N'@dbid int out', @dbid out
 
 
 SET @cmd = '
 USE '+ QUOTENAME(@db)+';
 INSERT INTO #tempcursor
    SELECT ''USE '+QUOTENAME(@db)+'; ALTER index [''+i.name+''] ON [''+TABLE_SCHEMA+''].[''+OBJECT_NAME(ips.object_id)+''] '+@subquery+';'' as valueReturn
    FROM sys.dm_db_index_physical_stats('+@dbid+', default, default, default, default) ips
       INNER JOIN sys.indexes i
       ON ips.index_id = i.index_id AND ips.object_id = i.object_id
       INNER JOIN INFORMATION_SCHEMA.TABLES 
       ON OBJECT_NAME(ips.object_id) = TABLE_NAME
       WHERE ips.object_id > 99 AND ips.avg_fragmentation_in_percent >= 60 AND
       ips.index_id > 0 AND ips.page_count > 1000;';
 EXECUTE (@cmd);
 --PRINT (@cmd);
 FETCH NEXT FROM dbcursor INTO @db;
 end
 close dbcursor;
 deallocate dbcursor;
 
 DECLARE idxCursor CURSOR
 FOR SELECT * FROM #tempcursor
 
OPEN idxCursor  
FETCH NEXT FROM idxCursor INTO @db;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
BEGIN TRY
 print @db;
 exec (@db);
END TRY
    BEGIN CATCH 
     PRINT 'ERROR : ' +  @db;
    END CATCH
FETCH NEXT FROM idxCursor INTO @db;
end
 
close idxCursor;
deallocate idxCursor;
 
drop table #tempcursor;
 
END

Quand je l'exécute sur mon serveur, je n'ai pas d'erreur, mais je ne peux pas le lancer sur le serveur du client en PROD et il n'a pas d'environnement de TEST.
Je vais également me replonger sur l'écriture proposée par SQLpro.

[SQLpro]

enfin !

A +

[Scregneugneuu]

Bonjour à vous

Alors, j'ai réussi à avoir un résultat avec :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
DECLARE @db sysname, @subquery NVARCHAR(32), @cmd NVARCHAR(max) = N''
 
DECLARE dbcursor CURSOR
FOR 
   SELECT name
   FROM   master.sys.databases
   WHERE  state_desc = 'ONLINE'  
     AND  database_id > 4
	 AND  source_database_id IS NULL
	 AND  is_read_only = 0
	 AND  dbo.F_HADR_IS_PRIMARY_DB_BY_ID(database_id) IS NULL OR dbo.F_HADR_IS_PRIMARY_DB_BY_ID(database_id) = 1
OPEN dbcursor;  
FETCH NEXT FROM dbcursor INTO @db;
WHILE @@FETCH_STATUS = 0
BEGIN
   print @db;
   IF CAST(SERVERPROPERTY('Edition') AS NVARCHAR(1024)) LIKE 'Enterprise %'
      SET @subquery = ' WITH (ONLINE = ON)'
   ELSE SET @subquery = '';
 
   SET @cmd = 'SELECT N''ALTER index [''+i.name+''] ON [' + @db + '].[''+s.name+''].[''+o.name+''] REBUILD '+@subquery+';''
               FROM   sys.dm_db_index_physical_stats(DB_ID('''+@db+'''), default, default, default, default) ips
                      INNER JOIN [' + @db + '].sys.indexes i
                         ON ips.index_id = i.index_id AND ips.object_id = i.object_id
                      INNER JOIN [' + @db + '].sys.objects AS o
		    			   ON i.object_id = o.object_id
                      INNER JOIN [' + @db + '].sys.schemas AS s
				    	   ON o.schema_id = s.schema_id
               WHERE  ips.avg_fragmentation_in_percent >= 30 
			     AND  ips.index_id > 0 
			     AND  ips.page_count > 64;';
   PRINT @cmd; 
   FETCH NEXT FROM dbcursor INTO @db;
END;
-- EXECUTE (@cmd);
CLOSE dbcursor
DEALLOCATE dbcursor

Voici le résultat obtenu :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
55
56
57
58
59
60
61
62
63
 
DDE
SELECT N'ALTER index ['+i.name+'] ON [DDE].['+s.name+'].['+o.name+'] REBUILD ;'
               FROM   sys.dm_db_index_physical_stats(DB_ID('DDE'), default, default, default, default) ips
                      INNER JOIN [DDE].sys.indexes i
                         ON ips.index_id = i.index_id AND ips.object_id = i.object_id
                      INNER JOIN [DDE].sys.objects AS o
		    			   ON i.object_id = o.object_id
                      INNER JOIN [DDE].sys.schemas AS s
				    	   ON o.schema_id = s.schema_id
               WHERE  ips.avg_fragmentation_in_percent >= 30 
			     AND  ips.index_id > 0 
			     AND  ips.page_count > 64;
TOTO
SELECT N'ALTER index ['+i.name+'] ON [TOTO].['+s.name+'].['+o.name+'] REBUILD ;'
               FROM   sys.dm_db_index_physical_stats(DB_ID('TOTO'), default, default, default, default) ips
                      INNER JOIN [TOTO].sys.indexes i
                         ON ips.index_id = i.index_id AND ips.object_id = i.object_id
                      INNER JOIN [TOTO].sys.objects AS o
		    			   ON i.object_id = o.object_id
                      INNER JOIN [TOTO].sys.schemas AS s
				    	   ON o.schema_id = s.schema_id
               WHERE  ips.avg_fragmentation_in_percent >= 30 
			     AND  ips.index_id > 0 
			     AND  ips.page_count > 64;
AdventureWorks
SELECT N'ALTER index ['+i.name+'] ON [AdventureWorks].['+s.name+'].['+o.name+'] REBUILD ;'
               FROM   sys.dm_db_index_physical_stats(DB_ID('AdventureWorks'), default, default, default, default) ips
                      INNER JOIN [AdventureWorks].sys.indexes i
                         ON ips.index_id = i.index_id AND ips.object_id = i.object_id
                      INNER JOIN [AdventureWorks].sys.objects AS o
		    			   ON i.object_id = o.object_id
                      INNER JOIN [AdventureWorks].sys.schemas AS s
				    	   ON o.schema_id = s.schema_id
               WHERE  ips.avg_fragmentation_in_percent >= 30 
			     AND  ips.index_id > 0 
			     AND  ips.page_count > 64;
AdventureWorks2016
SELECT N'ALTER index ['+i.name+'] ON [AdventureWorks2016].['+s.name+'].['+o.name+'] REBUILD ;'
               FROM   sys.dm_db_index_physical_stats(DB_ID('AdventureWorks2016'), default, default, default, default) ips
                      INNER JOIN [AdventureWorks2016].sys.indexes i
                         ON ips.index_id = i.index_id AND ips.object_id = i.object_id
                      INNER JOIN [AdventureWorks2016].sys.objects AS o
		    			   ON i.object_id = o.object_id
                      INNER JOIN [AdventureWorks2016].sys.schemas AS s
				    	   ON o.schema_id = s.schema_id
               WHERE  ips.avg_fragmentation_in_percent >= 30 
			     AND  ips.index_id > 0 
			     AND  ips.page_count > 64;
TSQL
SELECT N'ALTER index ['+i.name+'] ON [TSQL].['+s.name+'].['+o.name+'] REBUILD ;'
               FROM   sys.dm_db_index_physical_stats(DB_ID('TSQL'), default, default, default, default) ips
                      INNER JOIN [TSQL].sys.indexes i
                         ON ips.index_id = i.index_id AND ips.object_id = i.object_id
                      INNER JOIN [TSQL].sys.objects AS o
		    			   ON i.object_id = o.object_id
                      INNER JOIN [TSQL].sys.schemas AS s
				    	   ON o.schema_id = s.schema_id
               WHERE  ips.avg_fragmentation_in_percent >= 30 
			     AND  ips.index_id > 0 
			     AND  ips.page_count > 64;
 
Heure de fin*: 2020-04-09T10:50:59.3190935+02:00

[aieeeuuuuu]

il faudrait appliquer QUOTENAME partout, y compris dans la construction des commandes de reconstruction d'index...