CREATE VIEW sys.database_files AS
SELECT
file_id = f.fileid,
file_guid = f.fileguid,
type = f.filetype,
type_desc = ft.name,
data_space_id = f.grpid,
name = f.lname,
physical_name = f.pname,
state = convert(tinyint, case f.filestate -- Map enum EMDFileState to AvailablityStates
when 0 then 0 when 10 then 0 -- ONLINE
when 4 then 7 -- DEFUNCT
when 5 then 3 when 9 then 3 -- RECOVERY_PENDING
when 7 then 1 when 8 then 1 when 11 then 1 -- RESTORING
when 12 then 4 -- SUSPECT
else 6 end), -- OFFLINE
state_desc = st.name,
size = isnull(FilePropertyById(f.fileid, 'size'), size),
max_size = f.maxsize,
f.growth,
is_media_read_only = sysconv(bit, f.status & 8), -- FIL_READONLY_MEDIA
is_read_only = sysconv(bit, f.status & 16), -- FIL_READONLY
is_sparse = sysconv(bit, f.status & 256), -- FIL_SPARSE_FILE
is_percent_growth = sysconv(bit, f.status & 32), -- FIL_PERCENT_GROWTH
is_name_reserved = sysconv(bit, case f.filestate when 3 then 1 else 0 end), -- x_efs_DroppedReusePending
create_lsn = GetNumericLsn(f.createlsn),
drop_lsn = GetNumericLsn(f.droplsn),
read_only_lsn = GetNumericLsn(f.readonlylsn),
read_write_lsn = GetNumericLsn(f.readwritelsn),
differential_base_lsn = GetNumericLsn(f.diffbaselsn),
differential_base_guid = f.diffbaseguid,
differential_base_time = nullif(f.diffbasetime, 0),
redo_start_lsn = GetNumericLsn(f.redostartlsn),
redo_start_fork_guid = f.redostartforkguid,
redo_target_lsn = GetNumericLsn(f.redotargetlsn),
redo_target_fork_guid = f.forkguid,
backup_lsn = GetNumericLsn(f.backuplsn)
FROM sys.sysprufiles f
LEFT JOIN sys.syspalvalues st ON st.class = 'DBFS' AND st.value = f.filestate
LEFT JOIN sys.syspalvalues ft ON ft.class = 'DBFT' AND ft.value = f.filetype
WHERE filestate NOT IN (1, 2) -- x_efs_Dummy, x_efs_Dropped
|
