Get-SqlDatabase "WIN7W510\R2" "AdventureWorks" | Get-SqlDataFile | Format-Table Server, dbname, FileGroup, FileName, FreeSpace, timestamp

Import-Module SQLServer

SET NOCOUNT ON;

Declare 
	@TargetDatabase sysname,
	@Level varchar(10),
	@UpdateUsage bit,
	@Unit char(2)

Select @TargetDatabase = NULL,		--  NULL: all dbs
	@Level = 'File',				--  or "Database"
	@UpdateUsage = 0,				--  default no update
	@Unit = 'GB'					--  Megabytes, Kilobytes or Gigabytes

CREATE TABLE #Tbl_CombinedInfo (
	DatabaseName sysname NULL, 
	[type] VARCHAR(10) NULL, 
	FileGroup VARCHAR(50) NULL, 
	LogicalName VARCHAR(150) NULL,
	T dec(10, 2) NULL,
	U dec(10, 2) NULL,
	[U(%)] dec(5, 2) NULL,
	F dec(10, 2) NULL,
	[F(%)] dec(5, 2) NULL,
	PhysicalName sysname NULL );

CREATE TABLE #Tbl_DbFileStats (
	Id int identity, 
	DatabaseName sysname NULL, 
	FileId int NULL, 
	FileGroupID int NULL, 
	TotalExtents bigint NULL, 
	UsedExtents bigint NULL, 
	Name sysname NULL, 
	[FileName] varchar(255) NULL );
  
CREATE TABLE #Tbl_ValidDbs (
	Id int identity, 
	Dbname sysname NULL );
  
CREATE TABLE #Tbl_Logs (
	DatabaseName sysname NULL, 
	LogSize dec (10, 2) NULL, 
	LogSpaceUsedPercent dec (5, 2) NULL,
	Status int NULL );

DECLARE @Ver varchar(10), 
	@DatabaseName sysname, 
	@Ident_last int, 
	@String varchar(2000),
	@BaseString varchar(2000);
        
SELECT @DatabaseName = '', 
	@Ident_last = 0, 
	@String = '', 
	@Ver = CASE WHEN @@VERSION LIKE '%9.0%' THEN 'SQL 2005' 
		WHEN @@VERSION LIKE '%8.0%' THEN 'SQL 2000' 
		WHEN @@VERSION LIKE '%10.%' THEN 'SQL 2008' 
		END;

SELECT @BaseString = ' SELECT DB_NAME(), ' + 
	CASE WHEN @Ver = 'SQL 2000' THEN 'CASE WHEN a.status & 0x40 = 0x40 THEN ''Log''  ELSE ''Data'' END' 
		ELSE 'CASE type WHEN 0 THEN ''Data'' WHEN 1 THEN ''Log'' WHEN 4 THEN ''Full-text'' ELSE ''reserved'' END' 
	END 
	+ ', groupname, name, ' + 
	CASE WHEN @Ver = 'SQL 2000' THEN 'filename' 
		ELSE 'physical_name' 
	END 
	+ ', size*8.0/1024.0 FROM ' + 
	CASE WHEN @Ver = 'SQL 2000' THEN 'sysfiles a Left Join sysfilegroups b on a.groupid = b.groupid' 
		ELSE 'sys.database_files a Left Join sysfilegroups b on a.data_space_id = b.groupid' 
	END 
	+ ' WHERE ' + 
	CASE WHEN @Ver = 'SQL 2000' THEN ' HAS_DBACCESS(DB_NAME()) = 1' 
		ELSE 'state_desc = ''ONLINE''' 
	END 
	+ '';

SELECT @String = 'INSERT INTO #Tbl_ValidDbs SELECT name FROM ' + 
	CASE WHEN @Ver = 'SQL 2000' THEN 'master.dbo.sysdatabases' 
		WHEN @Ver IN ('SQL 2005', 'SQL 2008') THEN 'master.sys.databases' 
	END 
	+ ' WHERE HAS_DBACCESS(name) = 1 ORDER BY name ASC';
EXEC (@String);

INSERT INTO #Tbl_Logs EXEC ('DBCC SQLPERF (LOGSPACE) WITH NO_INFOMSGS');

BEGIN
	WHILE 1 = 1
	BEGIN
		SELECT TOP 1 @DatabaseName = Dbname 
			FROM #Tbl_ValidDbs 
			WHERE Dbname > @DatabaseName 
			ORDER BY Dbname;

		IF @@ROWCOUNT = 0
		BREAK;
		
		SELECT @Ident_last = ISNULL(MAX(Id), 0) 
			FROM #Tbl_DbFileStats;

		SELECT @String = 'INSERT INTO #Tbl_CombinedInfo (DatabaseName, type, FileGroup, LogicalName, PhysicalName, T) ' + @BaseString; 

		EXEC ('USE [' + @DatabaseName + '] ' + @String);

		INSERT INTO #Tbl_DbFileStats (FileId, FileGroupID, TotalExtents, UsedExtents, Name, FileName)
		EXEC ('USE [' + @DatabaseName + '] DBCC SHOWFILESTATS WITH NO_INFOMSGS');

		UPDATE #Tbl_DbFileStats 
			SET DatabaseName = @DatabaseName 
			WHERE Id BETWEEN @Ident_last + 1 
				AND @@IDENTITY;
	END
END

UPDATE #Tbl_CombinedInfo 
	SET U = s.UsedExtents*8*8/1024.0 
	FROM #Tbl_CombinedInfo t 
		JOIN #Tbl_DbFileStats s ON t.LogicalName = s.Name 
			AND s.DatabaseName = t.DatabaseName;

UPDATE #Tbl_CombinedInfo 
	SET [U(%)] = LogSpaceUsedPercent, 
		U = T * LogSpaceUsedPercent/100.0
	FROM #Tbl_CombinedInfo t 
		JOIN #Tbl_Logs l ON l.DatabaseName = t.DatabaseName 
	WHERE t.type = 'Log';

UPDATE #Tbl_CombinedInfo SET F = T - U, [U(%)] = U*100.0/T;

UPDATE #Tbl_CombinedInfo SET [F(%)] = F*100.0/T;

IF UPPER(ISNULL(@Level, 'DATABASE')) = 'FILE'
BEGIN
	IF @Unit = 'KB'
	UPDATE #Tbl_CombinedInfo
		SET T = T * 1024, U = U * 1024, F = F * 1024;

	IF @Unit = 'GB'
	UPDATE #Tbl_CombinedInfo
		SET T = T / 1024, U = U / 1024, F = F / 1024;

	SELECT Case When CAST(SERVERPROPERTY('InstanceName') as varchar(50)) is NULL 
		Then CAST(SERVERPROPERTY('MachineName')  as varchar(50))
		Else CAST(SERVERPROPERTY('InstanceName') as varchar(50))
		End as 'InstanceName',
		DatabaseName AS 'Database',
		type AS 'Type',
		FileGroup,
		LogicalName,
		T AS 'Total',
		U AS 'Used',
		[U(%)] AS 'Used (%)',
		F AS 'Free',
		[F(%)] AS 'Free (%)',
		PhysicalName
		FROM #Tbl_CombinedInfo 
		WHERE DatabaseName LIKE ISNULL(@TargetDatabase, '%') 
		ORDER BY DatabaseName, type;

END

DROP TABLE #Tbl_CombinedInfo
DROP TABLE #Tbl_DbFileStats
DROP TABLE #Tbl_ValidDbs
DROP TABLE #Tbl_Logs