-- ===============================================
-- Author: Pawel Potasinski
-- Create date: 2009-12-12
-- Script: Database files
-- Description: Display location, size, free space
--              for each database file
-- ===============================================

SET NOCOUNT ON;
DECLARE @temp TABLE (
  db nvarchar(256) NOT NULL,
  file_id	int	NOT NULL,
  type_desc	nvarchar(60) NOT NULL,
  name sysname NOT NULL,
  physical_name	nvarchar(260) NOT NULL,
  size int NOT NULL,
  space_used int NOT NULL
);
DECLARE @db sysname, @stmt nvarchar(4000);
DECLARE curDatabases CURSOR LOCAL STATIC
FOR SELECT name FROM sys.databases WITH (NOLOCK) WHERE state_desc = 'ONLINE' ORDER BY name;
OPEN curDatabases;
FETCH NEXT FROM curDatabases INTO @db;
WHILE @@FETCH_STATUS = 0 BEGIN
  SET @stmt = N'USE ' + QUOTENAME(@db) + N';' + NCHAR(13) + NCHAR(10) +
              N'SELECT 
                  DB_NAME() AS db, 
                  file_id, 
                  type_desc, 
                  name, 
                  physical_name, 
                  size, 
                  FILEPROPERTY(name, ''SpaceUsed'') 
                FROM sys.database_files WITH (NOLOCK)
                WHERE type IN (0,1)';
  INSERT INTO @temp (db, file_id, type_desc, name, physical_name, size, space_used)
  EXEC(@stmt);                
  FETCH NEXT FROM curDatabases INTO @db;
END
CLOSE curDatabases;
DEALLOCATE curDatabases;
SELECT
  db,
  file_id,
  type_desc,
  name, 
  physical_name,
  CONVERT(numeric(20,2),size*8192./(1024*1024)) AS size,
  CONVERT(numeric(20,2),(size-space_used)*8192./(1024*1024)) AS free_space
FROM @temp
-- ORDER BY free_space DESC; -- order by free space - *** REMEMBER, SHRINKING IS BAD!!! ***
ORDER BY LEFT(physical_name,1), size DESC; -- order by drive letter and size descendant

-------------------------------------------------
-- NOTE:
-- If you don't need information about free space 
-- in the files use master.sys.master_files view.
-------------------------------------------------