Discussion :

[Pierre.SRI]

Bonjour,

J'ai écrit un script SQL qui passe en revue l'ensemble des bases existantes sur un serveur. Pour chaque base, j'ai besoin d'exécuter une requête sur la table sys.database_files et de récupérer la valeur retournée, mais j'ai des problèmes de syntaxe.

Voici le script que boucle sur l'ensemble des 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

declare c cursor for 
  select name from   sysdatabases 
  where  name like 'DB%' and name <> 'DB000000'
  	          and  (databasepropertyex(name,'Status') = 'ONLINE')
  for read only

declare @name varchar(32)
declare @cmd  varchar(256)
declare @size integer

open c
fetch next from c into @name

while @@fetch_status = 0 
begin
  --
  -- ici les commandes à exécuter pour chaque base
  --
  fetch next from c into @name
end

close c
deallocate c

A l'intérieur de la boucle, j'ai essayé de faire un USE :

Code :

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

1
2
3
select @cmd = 'use ' + @name
  execute (@cmd)
  select size from sys.sysdatabase_files

Mais la requête ne s'exécute pas sur la base définie par @name mais sur MASTER.

Comment faire pour exécuter la requête sur la base définie par @name ?

J'ai essayé

Code :

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

select size from @name.sys.sysdatabase_files

qui renvoie une erreur de syntaxe n°156

J'ai aussi essayé de définir la syntaxe de ma requête dans une variable :

Code :

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

1
2
select @cmd = 'select size from ' + @name +'sys.sysdatabase_files'
 execute (@cmd)

Ce qui fonctionne, mais comment faire pour récupérer le résultat de la requête dans une variable ?

D'avance merci pour votre aide.

Environnement : SQL2005

[Chtulus]

Bonjour,

Il faut relancer un SELECT à la fin qui retourne le résultat après le DEALLOCATE.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
while @@fetch_status = 0 
begin
  --
  -- ici les commandes à exécuter pour chaque base
  --
  fetch next from c into @name
end
 
close c
deallocate c
 
IF @name IS NOT NULL
SELECT @name

En même temps il me semble qu'il est préférable d'éviter les curseur sur SQL Server.

[Pierre.SRI]

Merci, mais je ne pense pas que cela réponde à ma question.

La requête dont je veux récupérer le résultat dans une variable s'exécute à l'intérieur de la boucle, donc bien avant le DEALLOCATE.

Est-ce que la clause OUTPUT de l'instruction EXECUTE pourrait être utilisée ?

Bonjour,

Il faut relancer un SELECT à la fin qui retourne le résultat après le DEALLOCATE.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
while @@fetch_status = 0 
begin
  --
  -- ici les commandes à exécuter pour chaque base
  --
  fetch next from c into @name
end
 
close c
deallocate c
 
IF @name IS NOT NULL
SELECT @name

En même temps il me semble qu'il est préférable d'éviter les curseur sur SQL Server.

[Chtulus]

Re,

Regarde ce sujet de @SQLpro : Eviter les curseurs sur SQL server

[Pierre.SRI]

Intéressant sur le fond, mais comme je ne suis pas du tout un pro de SQL et que je n'ai pas envie de réécrire mon script de A à Z, je préfèrerais avoir une réponse sur les points évoqués dans mes précédents messages.

Parce que sur le principe mon script fonctionne à 99% Les cursors sont peut-être gourmand en ressource, mais comme l'exécution du script se fait à 04H00 du matin et que le serveur ne fait rien d'autre à ce moment, ce n'est pas vraiment grave.

Donc quelqu'un peut-il m'indiquer comment récupérer dans une variable le résultat de la commande EXECUTE ci-dessous ?

Code :

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

1
2
select @cmd = 'select size from ' + @name +'sys.sysdatabase_files'
 execute (@cmd)

Ou alors comment inclure dans la requête le nom de la table à traiter, défini par la variable @name. J'ai essayé comme ça mais SQL me dit que la syntaxe n'est pas bonne :

Code :

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

select @taille = (select size from @name.sys.sysdatabase_files)

Alors que si je mets un nom de base en dur (par exemple DBMOD001), ça fonctionne correctement :

Code :

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

select @taille = (select size from DBMOD001.sys.sysdatabase_files)

D'avance merci, et merci pour les réponses déjà apportées.

Re,

Regarde ce sujet de @SQLpro : Eviter les curseurs sur SQL server

[SQLpro]

Il faut que vous utilisiez la procédure stockée sp_execute_sql pour ce faire à la place du EXEC (...).

A +

[Pierre.SRI]

sp_executesql je présume (sans le second "_") ?

Effectivement, ça fonctionne, merci mille fois. J'ai un peu galéré pour trouver une syntaxe qui fonctionne . La voici, au cas où cela pourrait servir à d'autres débutants comme moi qui tomberaient sur cette discussion (je n'ai gardé que ce qui illustre la solution).

Code :

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

1
2
3
4
5
6
7
8
9
10
 
declare @name varchar(32)      
declare @cmd  nvarchar(256)   
declare @param nvarchar(256) 
declare @size varchar(32)      
 
set @name = 'DBMOD001'      
set @cmd = N'select @sizeOUT=size from ' + @name + '.sys.database_files where physical_name like ''%.LDF'''    
set @param= N'@sizeOUT varchar(32) OUTPUT'   
execute sp_executesql @cmd, @param, @sizeOUT=@size OUTPUT

A la sortie le résultat de la requête est affecté à la variable @size.

Il faut que vous utilisiez la procédure stockée sp_execute_sql pour ce faire à la place du EXEC (...).

A +