sp_MSforeachdb, sp_MSforeachtable 프로시저 활용하기

sp_MSforeachdb, sp_MSforeachtable 프로시저 활용하기

 

• Version : SQL Server 2008, 208R2, 2012

 

master 데이터베이스에 있는 sp_MSforeachtdb, sp_MSForeachtable 프로시저에 대해서 알아 보자. (필자도 업무용 쿼리를 분석하다 알게 되었다).

 

[sp_MSforeachdb]

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

 

아래 스크립트는 인스턴스내의 모든 데이터베이스의 helpfile 정보이다.

use master go   exec sp_MSforeachdb 'use ? exec sp_helpfile'

 

 

 

아래 스크립트는 인스터턴스 내의 모든 데이터베이스 파일 정보를 반환 한다.

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

 

 

 

다음 예제는 테이블 변수를 사용하여 테이블 변수에 데이터를 삽입 한다.

DECLARE @DatabasesSize TABLE ( name VARCHAR(50), physical_name VARCHAR(500), state BIT, size INT ) INSERT INTO @DatabasesSize EXEC sp_MSforeachdb 'IF ''?'' NOT IN (''tempDB'',''model'',''msdb'') BEGIN SELECT name,physical_name,state,size FROM ?.sys.database_files END'   select * from @DatabasesSize

 

 

 

다음 예제는 where 절에 use ? 를 사용한다.

EXEC sp_MSforeachdb 'IF ''?'' NOT IN (''tempDB'',''model'',''msdb'') BEGIN SELECT name,physical_name,state,size FROM ?.sys.database_files WHERE name LIKE ''?%'' -- Only Files starting with DB name END'

 

 

 

[sp_MSforeachtable]

sp_MSforeachtable 프로시저는 현재 데이터베이스 내의 모든 테이블 이름을 반환하며 이 테이블이름을 참조하여 지정된 반복문을 수행 한다.

 

아래 스크립트는 해당 데이터베이스에 존재하는 모든 테이블의 row count를 테이블에 삽입하여 출력한다.

begin try create table #rowcount (tablename varchar(128), rowcnt int) end try begin catch end catch   exec sp_MSforeachtable 'insert into #rowcount select ''?'', count(*) from ?' select top 5 * from #rowcount order by tablename drop table #rowcount

 

 

위의 두 시스템 프로시저를 잘 활용하면 사용자 프로시저 생성시 코드를 간결하고 빠르게 개발 할 수 있을 듯 하다.

 

[참고자료]

• http://www.codeproject.com/Articles/459536/SQL-Server-Applying-Filter-on-sp_MSforeachDB#
• http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm

 

 

2013-05-23 / 강성욱 / http://sqlmvp.kr