筆記 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
sp_MSforeachdb 的作用就是將 command 中的 ? 迭代為各個 Database 從而實現向各資料庫執行指令的用途。
此外上述的 Script 利用了 Declare Table 以及 Insert Into 來將 sp_MSforeachdb 執行結果保存在同一張資料表中,而非數次的查詢結果,十分便利。
但因為 sp_MSforeachdb 不是官方文件上正式收錄的預存程式,什麼時候會不支援就只能聽天由命了 🙄
參考資料
SQL Server: sp_MSforeachdb into single result set
How to list role members in SQL Server 2008 R2