MSSQL : sp_MSforeachdb, sp_MSforeachtable 활용하기

MS SQL의 sp_msforeachdb와 sp_msforeachtable이라는 두 가지 비공식적인 저장 프로시저에 대해 알아보고, 어떻게 사용하는지 예제를 통해 보도록 하겠습니다.

sp_msforeachdb란?

sp_msforeachdb는 SQL Server 인스턴스 내에 있는 모든 데이터베이스의 이름을 반환하고, 이 이름을 참조하여 지정된 반복문을 수행하는 저장 프로시저입니다. 즉, 한 번의 쿼리로 모든 데이터베이스에 대해 동일한 작업을 수행할 수 있습니다. 예를 들어, 모든 데이터베이스의 파일 정보를 조회하고 싶다면, 다음과 같은 쿼리를 사용할 수 있습니다.

EXEC sp_MSforeachdb 'BEGIN
SELECT name, physical_name, state, size
FROM ?.sys.database_files
END'

여기서 `?`는 데이터베이스 이름을 대체하는 토큰입니다. 즉, 인스턴스 내의 모든 데이터베이스에 대해 `sys.database_files` 시스템 뷰를 조회하는 쿼리가 실행됩니다. 만약 특정 데이터베이스를 제외하고 싶다면, `IF`문을 사용하여 조건을 걸 수 있습니다. 예를 들어, `tempDB`, `model`, `msdb` 데이터베이스를 제외하고 싶다면, 다음과 같이 쿼리를 수정할 수 있습니다.

EXEC sp_MSforeachdb 'IF ''?'' NOT IN (''tempDB'', ''model'', ''msdb'')
BEGIN
SELECT name, physical_name, state, size
FROM ?.sys.database_files
END'

sp_msforeachtable이란?

sp_msforeachtable은 현재 데이터베이스 내의 모든 테이블 이름을 반환하고, 이 테이블 이름을 참조하여 지정된 반복문을 수행하는 저장 프로시저입니다. 즉, 한 번의 쿼리로 모든 테이블에 대해 동일한 작업을 수행할 수 있습니다. 예를 들어, 모든 테이블의 행 개수를 조회하고 싶다면, 다음과 같은 쿼리를 사용할 수 있습니다.

EXEC sp_MSforeachtable 'SELECT ''?'', COUNT(*) FROM ?'

여기서 `?`는 테이블 이름을 대체하는 토큰입니다. 즉, 현재 데이터베이스 내의 모든 테이블에 대해 `COUNT(*)` 함수를 사용하여 행 개수를 조회하는 쿼리가 실행됩니다. 만약 특정 테이블을 제외하고 싶다면, `WHERE`절을 사용하여 조건을 걸 수 있습니다. 예를 들어, `sys` 스키마에 속하는 테이블을 제외하고 싶다면, 다음과 같이 쿼리를 수정할 수 있습니다.

EXEC sp_MSforeachtable 'SELECT ''?'', COUNT(*) FROM ? WHERE ''?'' NOT LIKE ''sys.%'''

sp_msforeachdb와 sp_msforeachtable을 함께 사용하기

sp_msforeachdb와 sp_msforeachtable을 함께 사용하면, 인스턴스 내의 모든 데이터베이스와 테이블에 대해 동일한 작업을 수행할 수 있습니다. 예를 들어, 모든 데이터베이스와 테이블의 행 개수를 조회하고 싶다면, 다음과 같은 쿼리를 사용할 수 있습니다.

EXEC sp_MSforeachdb 'USE ?; EXEC sp_MSforeachtable ''SELECT DB_NAME(), ''?'', COUNT(*) FROM ?'''

여기서 첫 번째 `?`는 데이터베이스 이름을, 두 번째 `?`는 테이블 이름을 대체하는 토큰입니다. 즉, 인스턴스 내의 모든 데이터베이스에 대해 `USE`문을 사용하여 데이터베이스를 변경하고, 그 데이터베이스 내의 모든 테이블에 대해 `DB_NAME()` 함수와 `COUNT(*)` 함수를 사용하여 데이터베이스 이름과 테이블 이름, 행 개수를 조회하는 쿼리가 실행됩니다.

sp_msforeachdb와 sp_msforeachtable의 주의사항

sp_msforeachdb와 sp_msforeachtable은 비공식적인 저장 프로시저이므로, 공식 문서에는 나와 있지 않고, 향후 버전에서 변경되거나 삭제될 수 있습니다. 따라서, 실제 운영 환경에서 사용하기 전에 충분히 테스트하고, 가능하면 직접 커서나 동적 SQL을 사용하여 반복문을 작성하는 것이 좋습니다. 또한, 이러한 저장 프로시저들은 일부 예외 상황을 처리하지 못하거나, 성능 문제가 발생할 수 있으므로, 사용할 때는 주의가 필요합니다.