[MsSQL] 모든 DB에 한번 쿼리 던지기 / DB 모를 때 (SP_MSFOREACHDB 활용하기)

자주 쓰는 쿼리 - SP_MSFOREACHDB 

특정 테이블을 찾고 싶은데, 어떤 데이터베이스에 있는지 모를 때, 혹은 여러 데이터베이스들을 돌아가며 파일사이즈를 조사할 때 같이 모든 데이터베이스에 같은 쿼리를 조회하고 싶을 때가 있다.

 

이럴 때 쓸수있는 시스템 프로시저로 단순 조회작업 뿐만 아니라 프로시저에 활용하여 로그성 데이터, 반복적인 쿼리 조회등을 단순화 하여 업무 능률을 향상 할 수 있다.

 

Query

사용방법은 매우 간단하다.

EXEC MASTER.dbo.SP_MSFOREACHDB 
    '
    use ?
    begin

      [QUERY]

    end 
    '

 

SP_MSFOREACHDB의 파라미터로 ' use ? begin  [쿼리문] end '  값을 넣어주면,  use ? 부분에 모든 데이터베이스가 한번씩 들어가 쿼리문을 실행하게 된다.

 

사용 예시 1

데이터베이스의 Table 마다의 Index 사용량을 조회하는 쿼리이다. 인덱스 사용량을 조회하는 쿼리는 아래의 글을 참고하자.

[DBA][MsSQL] Index 사용량 조회 / 미사용 Index 확인

각 데이터베이스를 하나씩 선택해서 조회하기 번거롭기에 해당 쿼리를 조합하여 사용해 보았다.

위와 같은 조회 쿼리를 실행시키면 모든 데이터베이스를 돌아다니며 조회를 하는데 시스템 데이터베이스도 함께 조회가 되는 것은 참고해두자.

이렇게 여러번의 조회창이 생성되어 한번에 확인 가능하다.

 

사용 예시 2

이번에는 Select 뿐만 아니라 Insert 를 하여 로그성으로 남겨보도록 한다.

반응형

각 데이터베이스의 데이터파일의 사이즈를 남겨보도록하자. 데이터파일 사이즈를 확인하는 쿼리는 아래의 글을 참고한다.

[MsSQL] 자주 쓰는 쿼리 - Data File Size, Data File 경로 확인 (MDF, NDF)

 

먼저 데이터를 저장하여 남겨둘 테이블을 생성한다. 

use BAN_DB

create table TB_FILE_SIZE_LIST (
    FL_GR varchar(100),
	FL_NM varchar(100),
	FL_PATH varchar(100),
	FULL_SIZE decimal(15, 2),
    USED_SIZE decimal(15, 2),
    FREE_SIZE decimal(15, 2)
)

BAN_DB라는 데이터베이스의 TB_FILE_SIZE_LIST라는 테이블을 생성하였다.

그 다음 아래와같이 Insert문을 지정하여 쿼리를 실행한다.

해당 테이블을 조회해보면 데이터들이 Insert되었음을 확인 할 수 있다.

 

 

Comment

잘 활용하면, 많은 도움이 될 만한 쿼리이다.

 

모든 데이터베이스에서 특정 문자가 포함된 프로시저를 찾는다던지, 특정 테이블을 찾는다던지 사용함에 따라 많은 도움이 될거라 생각한다.