[MSSQL] OPENROWSET 환경에서 sp_MSForEachDB 를 사용하는 방법

sp_MSForEachDB 란

---

각 데이터베이스에 쿼리를 수행하는 역할로 'USE ?' 구문과 같이 사용됩니다.

WITH RESULT SETS 이슈

---

OPENROWSET 과 sp_MSForEachDB SP 를 함께 사용하게 되면 WITH RESULT SETS 구문이 필요한 경우가 있습니다.

SP 에서 동적쿼리를 사용하게 되는 경우입니다.

사용하지 않는 경우 아래와 같은 오류가 발생합니다.

해결을 위해서는 아래 쿼리를 참고해 주세요.

메시지 11514, 수준 16, 상태 1, 프로시저 sys.sp_describe_first_result_set, 줄 1 [배치 시작 줄 0] 프로시저 'sp_MSforeachdb'의 문 'exec(@precommand)'에 동적 SQL이 있으므로 메타데이터를 확인할 수 없습니다. WITH RESULT SETS 절을 사용하여 결과 집합을 명시적으로 설명하십시오.

OPENROWSET 과 sp_MSForEachDB 사용 방법

---

쿼리에 대한 자세한 내용을 생략하겠습니다.

DECLARE @ServerIP varchar(20), @Query nvarchar(2000), @ExQuery nvarchar(4000)
 
SET @ServerIP='192.168.1.111'  -- Test Server
 
SET @Query = '
EXEC dbo.sp_MSForEachDB ''Use [?];
  SELECT
    @@SERVERNAME AS [ServerNAme]
    ,DB_NAME(t.[dbid]) AS [Database Name]
    ,t.text AS [TEXT]
  FROM
    sys.dm_exec_query_stats AS qs WITH (NOLOCK)
  CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
  CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
  WHERE
    1=1
    AND t.[dbid] = DB_ID(DB_NAME()) --특정DB 지정시
    AND t.[dbid] > 0                --전체DB조회
  ORDER BY qs.total_worker_time DESC ;''
'
 
set @ExQuery = 'SELECT * FROM OPENROWSET(''SQLOLEDB'', ''' + @ServerIP + ''';''TEST_Account'';''P@SSWORD'', ''' + replace(@Query, '''', '''''') + 'WITH RESULT SETS
(
(
  servername varchar(100)
  ,dbname varchar(100)
  ,[text] NVARCHAR(MAX)
)
)'')'
 
exec sp_executesql @ExQuery

부작용

---

Query 변수의 쿼리를 로컬서버에서 수행하면 모든 데이터베이스의 결과값이 출력됩니다.

아래 결과값은  A 로컬서버에서 수행했을 때와, 다른서버에서 OPENROWSET 으로 A 서버에 접속했을 때를 의미합니다.

A 로컬서버에서 수행했을 때

228개의 행이 출력되었고, master, msdb, 기타 등등의 데이터베이스의 결과가 수집되었습니다.

OPENROWSET 으로 A 서버에 접속하여 수행했을 때

master DB 인 104개의 행만 수집되었습니다.

다른 DB에서는 정상적으로 수집을 하지 못하였습니다.

결론

---

OPENROWSET 과 sp_MSForEachDB 를 함께 사용하는 법에 대해 알아봤습니다.

WITH RESULT SETS 의 오류를 해결하는 방법에 대해 알아보았습니다.

OPENROWSET 에서 sp_MSForEachDB 사용 시 모든 DB에서 수집되도록 하는것은 아직 숙제입니다.