SQLServer查看所有数据库⽂件信息  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))
sql2000数据库在哪个文件夹
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)
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.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

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。