迭代 SQL Server 資料庫執行指令的方式 - 使用 sp_MSforeachdb

2021-01-19

筆記 SQL Server 檯面下的法寶 sp_MSforeachdb ，可以用於迭代資料庫執行指令，例如針對每個資料庫查詢使用者，並彙整為一張資料表的便利。而之所以稱為檯面下，是因為雖然可以使用這個預存程式，但官方卻沒有任何的 documents 描述，同時也很有可能在未來的 SQL Server 版本不再支援，這點需要特別注意 🕵️

SQL Server Logo

說明

不囉嗦，直接看 Script：

DECLARE @command varchar(4000)
SELECT @command = '
USE [?];
WITH RoleMembers (member_principal_id, role_principal_id) 
AS 
(
  SELECT 
   rm1.member_principal_id, 
   rm1.role_principal_id
  FROM sys.database_role_members rm1 (NOLOCK)
   UNION ALL
  SELECT 
   d.member_principal_id, 
   rm.role_principal_id
  FROM sys.database_role_members rm (NOLOCK)
   INNER JOIN RoleMembers AS d 
   ON rm.member_principal_id = d.role_principal_id
)
select distinct ''?'' as database_name, mp.name as database_user, rp.name as database_role
from RoleMembers drm
  join sys.database_principals rp on (drm.role_principal_id = rp.principal_id)
  join sys.database_principals mp on (drm.member_principal_id = mp.principal_id)
order by rp.name
'

DECLARE @DatabasesKst TABLE
(
  database_name VARCHAR(50),
  database_user VARCHAR(50),
  database_role VARCHAR(50)
)

INSERT  INTO @DatabasesKst
EXEC sp_MSforeachdb @command

select * from @DatabasesKst

sp_MSforeachdb 的作用就是將 command 中的 ? 迭代為各個 Database 從而實現向各資料庫執行指令的用途。

此外上述的 Script 利用了 Declare Table 以及 Insert Into 來將 sp_MSforeachdb 執行結果保存在同一張資料表中，而非數次的查詢結果，十分便利。

但因為 sp_MSforeachdb 不是官方文件上正式收錄的預存程式，什麼時候會不支援就只能聽天由命了 🙄

參考資料

RiCo技術農場 - sp_MSforeachdb

五餅二魚工作室 - 快速完成多資料庫管理

SQL Server: sp_MSforeachdb into single result set

How to list role members in SQL Server 2008 R2

替代方案

Making a more reliable and flexible sp_MSforeachdb

A better sp_MSForEachDB

#Database #SQLServer