sp_MSforeachdb, sp_MSforeachtable 프로시저 활용

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

http://weblogs.sqlteam.com/joew/archive/2007/10/23/60383.aspx

SQL Server Undocumented Stored Procedures sp_MSforeachtable and sp_MSforeachdb

http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm

MS SQL Server Execute Undocumented Stored Procedures sp_MSForEachDB and sp_MSForEachTable with Example T-SQL Codes

http://www.kodyaz.com/articles/sql-server-exec-sp_msforeachdb-sp_msforeachtable-example-tsql-code.aspx

응용 프로그램 성능 최적화를 위한 숨겨진 데이터 찾기

http://msdn.microsoft.com/ko-kr/magazine/cc135978.aspx