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 |
위의 두 시스템 프로시저를 잘 활용하면 사용자 프로시저 생성시 코드를 간결하고 빠르게 개발 할 수 있을 듯 하다.
[참고자료]
강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp
