[MS-SQL] 프로시저에서 사용된 테이블 및 함수 추출, Table, Procedure, Function, sysdepends, sysobjects

-- 데이터베이스에 존재하는 모든 프로시저에서 사용된 테이블, 함수를 추출한다.

WITH sp_list AS (

 

    SELECT o.name AS proc_name

           , oo.name AS table_name

           , Row_number() OVER(partition by o.name, oo.name ORDER BY o.name, oo.name) AS row

      FROM sysdepends d

           INNER JOIN sysobjects o ON o.id = d.id

           INNER JOIN sysobjects oo ON oo.id = d.depid

     WHERE o.xtype = 'P'

)

SELECT proc_name, table_name

FROM sp_list a

WHERE row = 1 

ORDER BY proc_name, table_name

출처 : http://www.sqler.com/395037

※ 업무에서 사용하기 위해 본인이 진행한 과정.

 

1. 데이터베이스 표준화를 위해 현재 시스템에서 사용되는 테이블 목록을 뽑아내야 하는데...

2. 먼저, Class 구현(사용중인)된 프로시저 목록을 정리한다.

3. 사용중인 프로시저 목록이 정리되었다면 엑셀(CONCATENATE 함수)을 활용하여 

SELECT 'UP_CMM_AIRCRAFT_L' AS SP_NAME UNION ALL 와 같은 문자열을 생성한다.
4. 문자열이 준비되었다면, 아래와 같은 방법으로 임시테이블을 생성한다.

 

--(145개 행이 영향을 받음)

SELECT * INTO _bhchoi_used_sp

FROM (

             SELECT 'UP_CMM_AIRCRAFT_L' AS SP_NAME UNION ALL

             SELECT 'UP_SYS_FAQ_C' AS SP_NAME UNION ALL

             .

             .

             .

) a

 

5. 위의 쿼리를 준비하여 데이터베이스에 생성된 프로시저 목록을 뽑아낸다.

6. FROM 절에 아래와 같이 임시테이블을 Join하여 활용하여 추출하도록 한다.

 

INNER JOIN _bhchoi_used_sp b ON a.proc_name =b.sp_name

7. 결과를 수행해보면 데이터베이스에 생성된 프로시저와 임시테이블의 특정프로시저 목록을 비교하여 

    특정 프로시저에서 사용된 테이블 및 함수가 조회된다.