SQL Server でデータベース単位にクエリを実行したい場合の小ネタなどを。
SQL Server にはすべてのデータベース名を戻り値として返す sp_MSforeachdb というストアドプロシージャーがあります。
# sp_MSforeachtable というテーブルを返すストアドプロシージャーもあります。
このストアドプロシージャーを使用すると以下のようなクエリを実行することができます。
EXEC sys.sp_MSforeachdb 'USE ?;SELECT DB_NAME()'
実行すると以下のような結果を取得することができます。
ストアドプロシージャーを実行すると ? のパラメーターにデータベース名が入りますので、これを利用してデータベース単位にクエリを実行することができるようになります。
実際にどのようなクエリが実行されているかを確認してみます。
sp_helptext 'sp_MSforeachdb'
結果がこちら。
/*
* The following table definition will be created by SQLDMO at start of each connection.
* We don't create it here temporarily because we need it in Exec() or upgrade won't work.
*/
create proc sys.sp_MSforeachdb
@command1 nvarchar(2000), @replacechar nchar(1) = N'?', @command2 nvarchar(2000) = null, @command3 nvarchar(2000) = null,
@precommand nvarchar(2000) = null, @postcommand nvarchar(2000) = null
as
set deadlock_priority low
/* This proc returns one or more rows for each accessible db, with each db defaulting to its own result set */
/* @precommand and @postcommand may be used to force a single result set via a temp table. */
/* Preprocessor won't replace within quotes so have to use str(). */
declare @inaccessible nvarchar(12), @invalidlogin nvarchar(12), @dbinaccessible nvarchar(12)
select @inaccessible = ltrim(str(convert(int, 0x03e0), 11))
select @invalidlogin = ltrim(str(convert(int, 0x40000000), 11))
select @dbinaccessible = N'0x80000000' /* SQLDMODbUserProf_InaccessibleDb; the negative number doesn't work in convert() */
if (@precommand is not null)
exec(@precommand)
declare @origdb nvarchar(128)
select @origdb = db_name()
/* If it's a single user db and there's an entry for it in sysprocesses who isn't us, we can't use it. */
/* Create the select */
exec(N'declare hCForEachDatabase cursor global for select name from master.dbo.sysdatabases d ' +
N' where (d.status & ' + @inaccessible + N' = 0)' +
N' and (DATABASEPROPERTYEX(d.name, ''UserAccess'') <> ''SINGLE_USER'' and (has_dbaccess(d.name) = 1))' )
declare @retval int
select @retval = @@error
if (@retval = 0)
exec @retval = sys.sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 1
if (@retval = 0 and @postcommand is not null)
exec(@postcommand)
declare @tempdb nvarchar(258)
SELECT @tempdb = REPLACE(@origdb, N']', N']]')
exec (N'use ' + N'[' + @tempdb + N']')
return @retval
実際にはカーソルでぐるぐる回して動的にアドホッククエリを実行している形になります。
自前で実装しようとすると以下のようなクエリでしょうか。
DECLARE @db_name sysname DECLARE @sql nvarchar(max) DECLARE DB_CURSOR CURSOR FOR SELECT name FROM sys.databases OPEN DB_CURSOR FETCH NEXT FROM DB_CURSOR INTO @db_name WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = N'USE ' + @db_name + ';' SET @sql += 'SELECT ''' + @db_name + '''' EXEC (@sql) FETCH NEXT FROM DB_CURSOR INTO @db_name END CLOSE DB_CURSOR DEALLOCATE DB_CURSOR