sp_MSforeachdb

sp_MSforeachdb

sp_MSforeachdb 프로시저는 SQL Server 인스턴스 내에 있는 모든 데이터베이스의 이름을 반환하며 이 이름을 참조하여 지정된 반복문을 수행

 

 

1. sp_MSforeachdb란 무엇인가?

sp_MSforeachdb는 SQL Server에 기본 내장된 시스템 저장 프로시저(Stored Procedure) 중 하나로, 모든 데이터베이스를 순회하며 동일한 명령을 실행할 수 있도록 도와줍니다.

즉, for each database 루프처럼 동작하는 도구라고 보면 됩니다.

예를 들어,

• 모든 DB의 이름 확인
• 모든 DB에서 특정 테이블 크기 확인
• 일괄 백업 스크립트 실행

등 반복 작업을 간단하게 처리할 수 있습니다.

---

2. 기본 사용법

 

EXEC sp_MSforeachdb 'SQL문장'

여기서 'SQL문장' 안에서 ? 기호가 현재 실행 중인 데이터베이스 이름으로 치환됩니다.

📍 예제 1: 모든 DB 이름 조회

 

EXEC sp_MSforeachdb 'SELECT "?" AS DBName'

실행 결과:

DBName

master

tempdb

model

msdb

사용자DB1

사용자DB2

 

 

---

3. 자주 쓰는 활용 예제

📍 예제 2: 모든 DB에서 특정 테이블 존재 여부 확인

 

EXEC sp_MSforeachdb ' IF EXISTS (SELECT 1 FROM ?.sys.tables WHERE name = ''Users'') PRINT ''테이블 [Users] 가 존재하는 DB: ?'' '

📍 예제 3: 모든 DB의 사이즈 확인

 

EXEC sp_MSforeachdb ' USE ?; SELECT DB_NAME() AS DBName, SUM(size) * 8 / 1024 AS SizeMB FROM sys.master_files WHERE database_id = DB_ID() GROUP BY database_id '

📍 예제 4: 모든 DB에서 사용자 계정 확인

 

EXEC sp_MSforeachdb ' USE ?; SELECT DB_NAME() AS DBName, name, type_desc FROM sys.database_principals WHERE type IN (''S'', ''U'') '

---

4. 주의사항 🚨

1. 공식 문서에 없음 (Undocumented Feature)
   • Microsoft 공식 문서에는 없는 내부 프로시저입니다.
   • 차후 버전에서 제거되거나 동작 방식이 달라질 수 있습니다. (하지만 실무에서는 아직도 많이 씀)
2. 에러 처리 미흡
   • 일부 DB에 권한이 없으면 실행이 멈추지 않고 에러 메시지만 뿌림.
   • 따라서 관리자 권한이 필요하거나 TRY...CATCH와 함께 쓰는 것이 안전.
3. 병렬 실행 불가
   • 순차적으로 돌기 때문에 수십~수백 DB 환경에서는 시간이 오래 걸릴 수 있습니다.

---

5. sp_MSforeachdb 대체 방법

Microsoft는 sp_MSforeachdb 대신 **sys.databases**를 활용한 동적 SQL을 권장합니다.

📍 대체 코드 예시

 

DECLARE @sql NVARCHAR(MAX) = N''; SELECT @sql = @sql + ' USE [' + name + ']; SELECT DB_NAME() AS DBName, COUNT(*) AS TableCount FROM sys.tables;' FROM sys.databases WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb'); -- 필요시 제외 EXEC sp_executesql @sql;

이 방식은 공식적으로 지원되며, 유지보수에도 유리합니다.

---

6. 정리

• sp_MSforeachdb는 SQL Server에서 모든 DB를 대상으로 반복 실행할 수 있는 편리한 도구
• ? 기호를 활용해 현재 DB명을 치환 가능
• 주로 DB 관리, 모니터링, 테이블 검사, 사용자 계정 확인 등에 활용
• 하지만 비공식 기능이므로, 중요한 자동화 작업에는 동적 SQL + sys.databases 조합을 권장