SQL Server查看所有数据库文件信息

  SQL Server查看所有数据库文件信息, 通过自定义表,存储过程实现自动统计.

create table dbo.DBA_DBFileList
([DBID] int not null,
 DBName varchar(100) not null,
 FileId int,
 [FileGroup] varchar(50),
 FileType varchar(20),
 LogicFileName varchar(100),
 PhysicalFilename varchar(1000),
 FileSize decimal(10,2)                         -- GB
 constraint pk_DBA_DBFileList primary key([DBID],FileId)
)

-- 判断文件是否存在 返回0/1   0-文件不存在   1-文件存在
create function dbo.DBA_fileexist(@filename varchar(500)) 
returns int
as
begin
  declare @r int
  exec sys.xp_fileexist @filename,@r output 
  return @r
end
 
-- 获取所有数据库文件信息
create proc dbo.DBA_GetAllDatabaseFileInfo
as
begin
set nocount on

-- get all db file group
if object_id('tempdb..#dbfilegroup') is not null
  drop table #dbfilegroup

create table #dbfilegroup
([dbid] int,
 data_space_id int,
 FileGroupName varchar(50))

truncate table #dbfilegroup

declare @dbid int,@dbname varchar(100),@tsql varchar(6000)
declare ap scroll cursor for 
  select database_id,name from sys.databases where [state] not in(1,2,3,4,6)
/*
数据库状态[state] : 
0 = ONLINE
1 = RESTORING
2 = RECOVERING
3 = RECOVERY_PENDING
4 = SUSPECT
5 = EMERGENCY
6 = OFFLINE
*/

open ap
fetch first from ap into @dbid,@dbname
while(@@fetch_status<>-1)
begin
  select @tsql='select '+rtrim(@dbid)+',data_space_id,name from ['+@dbname+'].sys.filegroups '

  insert into #dbfilegroup(dbid,data_space_id,FileGroupName)
    exec(@tsql)

  fetch next from ap into @dbid,@dbname
end

close ap
deallocate ap

 truncate table dbo.DBA_DBFileList

-- get all db files
insert into dbo.DBA_DBFileList(DBID,DBName,FileId,FileGroup,FileType,LogicFileName,PhysicalFilename,FileSize)
  select a.database_id 'DBID',
             b.name 'DBName',
			 a.file_id 'FileId',
			 isnull(c.FileGroupName,'') 'FileGroup',
			 a.type_desc 'FileType',
			 a.name 'LogicFileName',
			 a.physical_name 'PhysicalFilename',
			 cast(a.size*8/1024/1024.0 as decimal(10,2)) 'FileSize'
  from master.sys.master_files a
  inner join master.sys.sysdatabases b on a.database_id=b.dbid
  left join #dbfilegroup c on a.database_id=c.dbid and a.data_space_id=c.data_space_id
  order by a.database_id,a.file_id

end