[MSSQL] 전체 데이터베이스 파일 정보 조회 (전체 DB 파일 목록 정보)

MSSQL에서 전체 데이터 베이스의 파일 정보를 조회하기 위한 내용입니다.

 

----------------------------------------------------------------------------------------------------

DECLARE @TBL_TEMP TABLE
(
 ROWNUM int,
 DBNAME VARCHAR(1000),
 QRY VARCHAR(1000)
) 

DECLARE @TBL_DB TABLE
(
 [NAME_DB] [varchar](255),
 [NAME_LOGICAL] [sysname],
 [file_id] [int],
 [physical_name] [nvarchar](4000),
 [size] [nvarchar](200)
)

DECLARE @QRY1 VARCHAR(4000);
DECLARE @i int, @maxCnt int; 

INSERT INTO @TBL_TEMP
SELECT
ROW_NUMBER() OVER(ORDER BY dtb.name)
,dtb.name AS [Name]
,' SELECT '''+ dtb.name + ''' NAME_DB, name NAME_LOGICAL,[file_id], physical_name, ''size'' = convert(nvarchar(50), convert (bigint, size) * 8 / 1024) + N'' MB''FROM ['+dtb.name+'].sys.database_files WHERE type_desc in (''ROWS'',''LOG'')' QRY
-- 조건절내에 WHERE 문으로 LDF, MDF 구분하여 조회 가능
FROM
master.sys.databases AS dtb
WHERE 1=1
AND state_desc = 'ONLINE'
ORDER BY [Name] ASC

SELECT @i = 1, @maxCnt = MAX(ROWNUM) FROM @TBL_TEMP;

WHILE @i<=@maxCnt
BEGIN
 SELECT @QRY1 = QRY FROM @TBL_TEMP WHERE ROWNUM = @i;
 
 INSERT  into @TBL_DB EXEC  (@QRY1)

 SET @i = @i + 1;

END;

SELECT *
fROM @TBL_DB

----------------------------------------------------------------------------------------------------