2021-01-19

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

說明
參考資料
1. 替代方案

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

說明

不囉嗦，直接看 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