Exécuter un script sur toutes les bases de données d'une instance

Exécuter un script sur toutes les bases de données d’une instance

25 avril 2017

Commentaires fermés

La procédure stockée système sp_MSforeachdb permet d’exécuter un script sur toutes les bases de données d’une instance. Le nom de la base correspond au point d’interrogation. Dans l’exemple ci-dessous, on lance une recherche d’objet par le nom dans toutes les bases de données utilisateur.

DECLARE @SqlExec VARCHAR(MAX),
		@ObjectName VARCHAR(100) = 'Address'

DECLARE @Result AS TABLE (DatabaseId INT, DatabaseName SYSNAME, SchemaName SYSNAME, ObjectName SYSNAME)

SET @SqlExec = '

IF DB_ID(''?'') BETWEEN 5 AND 32766 AND DATABASEPROPERTYEX(''?'', ''Status'') = ''ONLINE''
BEGIN

	USE [?];

	SELECT	DB_ID() AS DatabaseId,
			DB_NAME() AS DatabaseName,
			SCHEMA_NAME(schema_id) AS SchemaName,
			name AS ObjectName
	FROM	sys.objects
	WHERE	name = ' + CHAR(39) + @ObjectName + CHAR(39) + '

END'

PRINT @SqlExec

INSERT	INTO @Result
EXEC sp_MSforeachdb @SqlExec

SELECT	*
FROM	@Result

DECLARE @SqlExec VARCHAR(MAX),

@ObjectName VARCHAR(100) = 'Address'

DECLARE @Result AS TABLE (DatabaseId INT, DatabaseName SYSNAME, SchemaName SYSNAME, ObjectName SYSNAME)

SET @SqlExec = '

IF DB_ID(''?'') BETWEEN 5 AND 32766 AND DATABASEPROPERTYEX(''?'', ''Status'') = ''ONLINE''

BEGIN

USE [?];

SELECT DB_ID() AS DatabaseId,

DB_NAME() AS DatabaseName,

SCHEMA_NAME(schema_id) AS SchemaName,

name AS ObjectName

FROM sys.objects

WHERE name = ' + CHAR(39) + @ObjectName + CHAR(39) + '

END'

PRINT @SqlExec

INSERT INTO @Result

EXEC sp_MSforeachdb @SqlExec

SELECT *

FROM @Result

Autre exemple, dans le script ci-dessous, pour passer toutes les bases de données en mode de récupération simple, on pourra procéder ainsi :

EXEC sp_MSforeachdb '
IF DB_ID(''?'') BETWEEN 5 AND 32766 AND DATABASEPROPERTYEX(''?'', ''Status'') = ''ONLINE'' AND DatabasePropertyEx(''?'', ''Recovery'') =''FULL'' 
BEGIN
	EXEC (''ALTER DATABASE [?] SET RECOVERY SIMPLE;'')
END'