SQL Server Performance Forum – Threads Archive

List databases with their size

I want to list my databases, and physical related files, and their size but the following commands do not provide the size. Any idea? <pre id="code"> SELECT * FROM sysdatabases SELECT * FROM sys.sysfiles </pre id="code"> I know it’s silly question [<img src=’/community/emoticons/emotion-3.gif’ alt=’ :eek:

‘ />)] CanadaDBA

Found: SELECT Name [Database], Physical_Name [Physical file], size*8 [Size_KB] FROM sys.master_files
CanadaDBA

begin try declare @tab1 table(
file_group_name varchar(500) collate database_default
, logical_file_name varchar(500) collate database_default
, physical_file_name varchar(500) collate database_default
, space_reserved float
, space_reserved_unit varchar (5) collate database_default
, space_used float
, space_used_unit varchar (5) collate database_default
);
declare @filestats_temp_table table(
file_id int
, file_group_id int
, total_extents int
, used_extents int
, logical_file_name varchar(500) collate database_default
, physical_file_name varchar(500) collate database_default
); insert into @filestats_temp_table
exec (‘DBCC SHOWFILESTATS’); insert into @tab1
select t2.name as [file_group_name]
, t1.logical_file_name
, t1.physical_file_name
, case when (total_extents * 64) < 1024 then (total_extents * 64)
when (total_extents * 64 / 1024.0) < 1024 then (total_extents * 64 / 1024.0)
else (total_extents * 64 / 1048576.0)
end as space_reserved
, case when (total_extents * 64) < 1024 then ‘KB’
when (total_extents * 64 / 1024.0) < 1024 then ‘MB’
else ‘GB’
end as space_reserved_unit
, case when (used_extents * 64) < 1024 then (used_extents * 64)
when (used_extents * 64 / 1024.0) < 1024 then (used_extents * 64 / 1024.0)
else (used_extents * 64 / 1048576.0)
end as space_used
, case when (used_extents * 64) < 1024 then ‘KB’
when (used_extents * 64 / 1024.0) < 1024 then ‘MB’
else ‘GB’
end as space_used_unit
from @filestats_temp_table t1
inner join sys.data_spaces t2 on ( t1.file_group_id = t2.data_space_id ); select (row_number() over (order by file_group_name))%2 as l1
, db_name()
, file_group_name
, logical_file_name
, physical_file_name
, cast(space_reserved as decimal(10,2)) as ‘space_reserved’
, space_reserved_unit
, cast (space_used as decimal (10,2)) as ‘space_used’
, space_used_unit
from @tab1
end try
begin catch
select -100 as l1
, ERROR_NUMBER() as file_group_name
, ERROR_SEVERITY() as logical_file_name
, ERROR_STATE() as physical_file_name
, ERROR_MESSAGE() as space_reserved
, 1 as space_reserved_unit, 1 as space_used, 1 as space_used_unit
end catch ;
MohammedU.
Moderator
SQL-Server-Performance.com

The following will give you total, used and free space… set nocount on declare @cmd varchar(500) declare @db varchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> create table #results(FileType varchar(4) NOT NULL, [Name] sysname NOT NULL, Total numeric(9,2) NOT NULL, Used numeric(9,2) NOT NULL, [Free] numeric(9,2) NOT NULL, dbname sysname NULL) create table #data(Fileid int NOT NULL, [FileGroup] int NOT NULL, TotalExtents int NOT NULL, UsedExtents int NOT NULL, [Name] sysname NOT NULL, [FileName] varchar(300) NOT NULL) create table #log(dbname sysname NOT NULL, LogSize numeric(15,7) NOT NULL, LogUsed numeric(9,5) NOT NULL, Status int NOT NULL) begin /* Get data file(s) size */ declare dcur cursor local fast_forward for select NAME from sys.databases open dcur fetch next from dcur into @db while @@fetch_status=0 begin set @cmd = ‘use ‘ + @db + ‘ DBCC showfilestats’ insert #data exec(@cmd) insert #results(FileType,[Name],Total,Used,[Free],dbname) select ‘Data’, left(right([FileName],charindex(”,reverse([FileName]))-1), charindex(‘.’,right([FileName], charindex(”,reverse([FileName]))-1))-1), CAST(((TotalExtents*64)/1024.00) as numeric(9,2)), CAST(((UsedExtents*64)/1024.00) as numeric(9,2)), (CAST(((TotalExtents*64)/1024.00) as numeric(9,2)) -CAST(((UsedExtents*64)/1024.00) as numeric(9,2))), @db from #data delete #data fetch next from dcur into @db end close dcur deallocate dcur /* Get log file(s) size */ insert #log exec(‘dbcc sqlperf(logspace)’) insert #results(FileType,[Name],Total,Used,[Free],dbname) select ‘Log’,dbname+’_log’,LogSize, ((LogUsed/100)*LogSize),LogSize-((LogUsed/100)*LogSize), dbname from #log select dbname,FileType,[Name],Total,Used,[Free] from #results order by dbname,FileType select @@servername as servername, dbname, sum(total) Total, sum(used) Used, sum(free) Free from #results group by dbname order by Total desc drop table #data drop table #log drop table #results return end MohammedU. Moderator SQL-Server-Performance.com

]]>