master 데이터베이스에 숨어 있는 sp_MSforeachtable, sp_MSforeachdb 프로시저에 대해서 살짝 정리해 봅니다. 이 프로지서는 Books Online 에 문서화 되어 있지 않습니다만 이미 많은 사람들의 블로그를 통해서 활용 예제들이 공개되어 있습니다. 저도 슬쩍.... 포스팅에 동참해 봅니다.
sp_MSforeachdb 프로시저는 인스턴스 내에 있는 모든 데이터베이스에 대한 이름을 반환하며 이 이름으로 지정된 작업을 반복(전체 데이터베이스 각각의 이름을 대입) 처리할 수 있는 기능이 제공됩니다.
다음은 인스턴스 내 설치된 모든 데이터베이스의 helpfile 정보입니다.
USE MASTER
GO
EXEC sp_MSForEachDB 'Use [?]; EXEC sp_helpfile'
수행 결과는 아래와 같습니다.
sp_MSforeachtable 프로시저도 마찬가지입니다. 이름에서 볼 수 있듯이 이번에는 특정 데이터베이스에 포함된 모든 테이블을 대상으로 지정된 작업을 수행할 수 있는 기능이 제공됩니다.
일반적으로 접하는 가장 흔한 예제가 현재 데이터베이스에 포함된 모든 테이블의 디스크 공간을 확인하는 작업입니다. 테이블 사용량을 매일 일정 주기로 Text 로 내려받는 저장 프로시저 스크립트가 많은 블로그에 공개되어 있었습니다.
USE AdventureWorks
GO
EXECUTE sp_MSforeachtable 'sp_spaceused [?];'
수행 결과는 아래와 같습니다.
이 밖에도 sp_changeobjectowner, DBCC CHECKTABLE, DBCC DBINFO 등과 함께 많은 응용하여 사용할 수 있습니다. 아래 참고자료의 링크를 참조하시면 다양한 예제들을 확인하실 수 있습니다.
[참고자료]
The undocumented sp_MSforeachtable procedure
SQL Server Undocumented Stored Procedures sp_MSforeachtable and sp_MSforeachdb
MS SQL Server Execute Undocumented Stored Procedures sp_MSForEachDB and sp_MSForEachTable with Example T-SQL Codes
작성자 : Lai Go / 작성일자 : 2009.07.28
