SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server でデータベース単位にクエリを実行したい場合の方法

leave a comment

SQL Server でデータベース単位にクエリを実行したい場合の小ネタなどを。

SQL Server にはすべてのデータベース名を戻り値として返す sp_MSforeachdb というストアドプロシージャーがあります。
# sp_MSforeachtable というテーブルを返すストアドプロシージャーもあります。

このストアドプロシージャーを使用すると以下のようなクエリを実行することができます。

EXEC sys.sp_MSforeachdb 'USE ?;SELECT DB_NAME()'

 

実行すると以下のような結果を取得することができます。

image

ストアドプロシージャーを実行すると ? のパラメーターにデータベース名が入りますので、これを利用してデータベース単位にクエリを実行することができるようになります。

実際にどのようなクエリが実行されているかを確認してみます。

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

Written by masayuki.ozawa

12月 12th, 2013 at 12:24 am

Posted in SQL Database

Tagged with

Leave a Reply

*