SELECT
name AS [Database Name]
, physical_name AS current_file_location
,type_desc
,size*8/1024/1024 [Database size]
FROM sys.master_files
order by name
go
---------------------------------------------------------------------------------------
SELECT
@@SERVERNAME [Server Name]
,d.NAME as [Name]
--,ROUND(SUM(CAST(mf.size AS bigint)) * 8 / 1024, 0) Size_MBs
,(SUM(CAST(mf.size AS bigint)) * 8 / 1024) / 1024 AS Size_GBs
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4 -- Skip system databases
GROUP BY d.NAME
ORDER BY d.NAME
---------------------------------------------------------------------------------------------------------------
USE MASTER
GO
create table #temp (
Instance sysname,
DB sysname,
File_ID int,
Type_Desc varchar(255),
FileName varchar(255),
Physical_Name varchar(255),
State_Desc varchar(255),
Rawsize Bigint,
SizeGB Int,
Is_Read_Only bit,
FGName varchar(255)
)
EXEC sp_msforeachdb 'use [?];
INSERT INTO #temp(Instance, DB, File_ID, Type_Desc, FileName, Physical_name, State_Desc, Rawsize, SizeGB, Is_Read_Only, FGName)
SELECT
@@servername as Instance,
db_name() as DB,
File_ID, df.Type_Desc,
df.Name FileName,
physical_name,
state_desc,
size RawSize,
(size * 8) / 1024 / 1024 as SizeGB,
df.is_read_only,
fg.name FGName
FROM sys.database_files df
left join
sys.filegroups fg
ON
df.data_space_id = fg.data_space_id'
select * from #temp ORDER BY DB