SQL Server에서 특정 디렉토리의 파일 목록을 가져올 때 주로 사용되는 방법은 xp_dirtree와 xp_cmdshell 확장 저장 프로시저입니다. 각각의 장단점이 있으므로 상황에 맞게 선택할 수 있습니다.

방법 1: xp_dirtree 활용 (권장)

xp_dirtree는 디렉토리 탐색 전용 확장 저장 프로시저로, 별도의 보안 설정 없이 테이블 형태로 결과를 반환합니다. xp_cmdshell을 활성화할 필요가 없어 보안상 이점이 있습니다.

1.1 기본 사용법

세 번째 인자를 1로 설정하면 파일까지 포함한 전체 목록을 조회합니다.

-- 네트워크 경로: \\192.168.1.1\bak
EXEC master.dbo.xp_dirtree '\\192.168.1.1\bak', 1, 1;

-- 로컬 경로: D:\bak\backup
EXEC master.dbo.xp_dirtree '\\.\D:\bak\backup', 0, 1;

인자 설명:

• 첫 번째: 대상 폴더 경로
• 두 번째: 1(현재 폴더만), 0 또는 생략(모든 하위 폴더 포함)
• 세 번째: 1(파일 포함), 0 또는 생략(폴더만)

반환 열:

• subdirectory: 항목 이름
• depth: 깊이 (여기서는 고정값 1)
• file: 0(폴더), 1(파일)

1.2 임시 테이블에 저장 후 가공

.bak 파일만 추출하거나 추가 처리가 필요할 때 유용합니다.

CREATE TABLE #TempFiles (
    ItemName NVARCHAR(512),
    Level INT,
    IsFile BIT
);

INSERT INTO #TempFiles (ItemName, Level, IsFile)
EXEC master.dbo.xp_dirtree '\\192.168.1.1\bak', 1, 1;

SELECT ItemName AS FileName
FROM #TempFiles
WHERE IsFile = 1;

DROP TABLE #TempFiles;

방법 2: xp_cmdshell을 이용한 dir 명령

Windows 명령 프롬프트의 dir 명령어를 실행하므로 더 세밀한 제어가 가능하지만, xp_cmdshell 활성화가 필요합니다.

사전 설정

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

실행 예제

DECLARE @command NVARCHAR(2000);
SET @command = 'dir /b /a-d "\\192.168.1.1\bak\*.bak"';

CREATE TABLE #DirResult (OutputLine NVARCHAR(500));

INSERT INTO #DirResult (OutputLine)
EXEC master.dbo.xp_cmdshell @command;

SELECT OutputLine FROM #DirResult WHERE OutputLine IS NOT NULL;

DROP TABLE #DirResult;

/b는 이름만 출력, /a-d는 디렉터리 제외를 의미합니다.

방법 3: 특정 파일 존재 여부 확인

전체 목록이 필요 없고 단일 파일의 존재만 확인하려면 xp_fileexist가 효율적입니다.

-- 결과 집합으로 확인
EXEC master.dbo.xp_fileexist '\\192.168.1.1\bak\sample.bak';

-- 출력 매개변수로 확인
DECLARE @exists INT;
EXEC master.dbo.xp_fileexist '\\192.168.1.1\bak\sample.bak', @exists OUTPUT;
SELECT @exists AS IsExists; -- 1: 존재, 0: 없음

실무 권장 사항

파일 목록을 조회한 후 삭제나 이동 같은 추가 작업이 필요하다면 xp_dirtree 방식이 가장 적합합니다. 보안 위험이 적고, 결과를 임시 테이블에 저장하여 SQL로 직접 처리할 수 있기 때문입니다. xp_cmdshell은 꼭 필요한 경우에만 사용하고, 사용 후에는 즉시 비활성화하는 것이 좋습니다.