데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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

No. Subject Author Date Views
1791 메모리 관리 아키텍처 – 버퍼 관리_페이지 쓰기 jevida(강성욱) 2016.09.28 1056
1790 메모리 관리 아키텍처 – 버퍼 관리_페이지 읽기 jevida(강성욱) 2016.09.28 1250
1789 메모리 관리 아키텍처 – 버퍼 관리 jevida(강성욱) 2016.09.28 1818
1788 메모리 관리 아키텍처 – Min/Max Server Memory 효과 jevida(강성욱) 2016.09.28 2557
1787 메모리 관리 아키텍처 – 동적 메모리 관리 jevida(강성욱) 2016.09.28 1365
1786 메모리 관리 아키텍처 – 프로세스 주소 공간 jevida(강성욱) 2016.09.28 1363
1785 메모리 관리 아키텍처 – 메모리 아키텍처 jevida(강성욱) 2016.09.28 1873
1784 데이터 압축 상태에 대한 개체 크기 예상 jevida(강성욱) 2016.09.28 1368
» sp_MSforeachdb, sp_MSforeachtable 프로시저 활용하기 jevida(강성욱) 2016.09.28 2887
1782 SQL Server 쿼리 처리 아키텍처_분산 쿼리 아키텍처 jevida(강성욱) 2016.09.28 1145
1781 SQL Server 쿼리 처리 아키텍처_병렬 쿼리 처리 - 병렬 인덱스 작업 jevida(강성욱) 2016.09.28 1341
1780 SQL Server 쿼리 처리 아키텍처_병렬 쿼리 처리 - 병렬 처리 수준 jevida(강성욱) 2016.09.28 1853
1779 SQL Server 쿼리 처리 아키텍처_병렬 쿼리 처리 jevida(강성욱) 2016.09.28 1791
1778 SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 - Preparing SQL Statements jevida(강성욱) 2016.09.28 1009
1777 SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 - 강제 매개 변수화 jevida(강성욱) 2016.09.28 999
1776 SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 - 단순 매개 변수화 jevida(강성욱) 2016.09.28 880
1775 SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 - 매개 변수 및 실행 계획 재사용 jevida(강성욱) 2016.09.28 1098
1774 SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 jevida(강성욱) 2016.09.28 1440
1773 DMV를 이용한 캐시된 저장 프로시저 통계 정보 확인 jevida(강성욱) 2016.09.28 989
1772 SQL Server 쿼리 처리 아키텍처_저장 프로시저 및 트리거 실행 jevida(강성욱) 2016.09.27 941





XE Login