Discussion :

[Ultr4]

Bonjour à tous,

Je cherche comment passer une requete, sur une multitudes de bases. (SQL 2008 R2 Standard)
Sur un autre sujet, SQLpro m'a conseillé de faire une requete en SQL dynamique. (Mais je ne sais pas comment ça marche)

Existe-t-il une requete ALL DATABASE, ou quelque chose du genre ?

Dans mon cas , il s'agirait d'appliquer des droits à un utilisateur, sur toutes les bases.

J'espère avoir posté dans la bonne rubrique.

Merci

[SQLpro]

Deux solutions :

1) utilisez EXEC sp_MSforeachdb

Code :

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

1
2
3
4
-- via sp_MSforeachdb
DECLARE @SQL NVARCHAR(max)
SET @SQL = N'USE ?;SELECT * FROM INFORMATION_SCHEMA.TABLES;';
EXEC sp_MSforeachdb @SQL

2) Utilisez du SQL dynamique

Code :

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

1
2
3
4
5
6
-- via SQL dynamique :
DECLARE @SQL_DYN NVARCHAR(max);
SET @SQL_DYN = N'';
SELECT @SQL_DYN = @SQL_DYN + 'SELECT * FROM [' + name + '].INFORMATION_SCHEMA.TABLES;'
FROM   sys.databases;
EXEC (@SQL_DYN);

A +

[Ultr4]

Encore un grand merci SQLpro !

[hmira]

L'utilisation du SQL Dynamique telle que préconisée par SQLPro donne à mon avis beaucoup plus de souplesse comparée à sp_MSforeachdb. Et effectivement, SQLPro a tout à fait raison.

Même si la procédure sp_MSforeachdb n'est pas documentée et donc, de ce fait, potentiellement sujette à caution, celle-ci est largement utilisée par les DBA et considérée par beaucoup très pratique (sauf par par moi-même !)

Personnellement, et cela n'engage que moi, je préfère de loin l'utilisation du SQL dynamique au travers l'utilisation de la procédure système sp_executesql.

En effet, alors que la procédure système sp_executesql est largement connue et utilisée par les développeurs et les DBA, il y a cependant un aspect très peu connu de cette procédure système sp_executesql et pourtant très pratique pour ne pas dire très puissant.

Il s'agit du fait que dès lors la procédure système sp_executesql est qualifiée (ou préfixée) par le nom de la base de données puis par le nom schéma en l'occurrence sys, le traitement dynamique s'exécute dans le contexte de la base de données mentionnée dans le chaine de qualification (le préfixe). Exemple :

Code SQL :

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

EXEC Nom_de_la_base.sys.sp_executesql N' .. Traitement divers ...';

Dans cet exemple simple, le ".. traitement divers ..." s' exécute dans le contexte de la base mentionnée dans le préfixe Nom_de_la_base, ce qui est déjà très pratique en soit.

Mais la subtilité de la chose ne s'arrête pas là !

Il n'est, par ailleurs, pas nécessaire que l'appel de la procédure système elle-même sp_executesql qu'elle soit qualifiée ou pas, soit mentionnée en "dur" dans l'instruction T-SQL.
Il est tout à fait possible, lors de l'appel, de rendre dynamique également le nom de la procédure système, qualifiée par le nom de la base et par le nom du schéma, sp_executesql elle-même. Là comme vous pouvez aisément le deviner la souplesse du ce système de construction est à son comble, elle augure des possibilités insoupçonnées.

Ci-dessous un exemple complet, qui illustre mes propos.

1 - Création d'un jeu de test

Code SQL :

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
USE DB_GESCOM    -- Adapter à votre environnement le nom de la base de données de test  
GO  
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
 
CREATE TABLE [dbo].[Client](
	IdClient int NOT NULL IDENTITY(1,1) , 
	Nom  varchar(60) NULL,
 CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED 
(
	idClient ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
INSERT INTO dbo.Client 
values ('Jean-Paul Sartre'), 
       ('Albert Camus'), 
	   ('Emmanuel Kant'); 
GO

2 - Construction dynamique et de l'appel de la procédure sp_executesql elle-même et du traitement (statement)

Code SQL :

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
USE Master   -- Notez le "use master".   On n'est pas du tout dans le contexte de notre base utilisateur DB_GSCOM 
 GO 
 DECLARE         
      @cDataBaseName  sysname  
    , @cSchemaName sysname 
    , @cTableName  sysname   
    , @cSqlStmt nvarchar(max) 
    , @cParamsDefinition   nvarchar(max) 
    , @iIdClient    int
    , @cCmdExecuteSqlInContextDB nvarchar(4000); 
 
SET @cDataBaseName = N'DB_GESCOM';   -- Adapter à votre environnement le nom de la base de données de test  
SET @cSchemaName   = N'dbo' 
SET @cTableName    = N'Client' 
 
SET @cSqlStmt = N'SELECT Nom FROM ['+@cSchemaName + N']'+N'.'+N'['+@cTableName +N'] WHERE IdClient = @prm_IdClient' 
SET @cParamsDefinition = N'@prm_IdClient int'; 
 
SET @iIdClient  = 2 
 
SET @cCmdExecuteSqlInContextDB = N'[' + @cDataBaseName + N'].sys.sp_executesql'; 
-- PRINT @cCmdExecuteSqlInContextDB 
-- PRINT @cSqlStmt 
-- PRINT @cParamsDefinition 
EXEC @cCmdExecuteSqlInContextDB  @cSqlStmt, @cParamsDefinition, @iIdClient

Résultat :

Code :

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

1
2
3
Nom
-----------------
Albert Camus

Remarques :
. Notez le "use master" au début du 2ème script, on n'est donc pas du tout dans le contexte de notre base utilisateur DB_GSCOM
. Notez la construction dynamique de l'appel lui-même de la procédure système qualifiée sp_executesql. Ce qui n'est, du premier abord, ni intuitif, ni naturel !
. Notez enfin l'aspect dynamique habituel, connu, de la chaîne du traitement (@cSqlStmt, @cParamsDefinition, @iIdClient)


A+

[aserf]

Bonjour,

Si vous avez besoin de faire la même chose sur plusieurs databases mais sur des serveurs différents, j'ai développer un logiciel pour sa, il ne fait que le "select" pour l'instant.
Mais envoyer moi un message en mail privé et je vous rajouterai alors les autres query (Update,Delete,Insert).

pour voir ce que sa fait, regarder la vidéo

Cordialement,