How big are your SQL Server database files? Where are they?

This is another quick piece that I run when I take on a new project.  Not too extensive, it just returns some brief details regarding your database files, sizes and locations.  Run it for one database, or every db on the server.  You might also consider using this within an Agent job, to report the file state to your DBA Team, on a recurring basis.

IF OBJECT_ID('usp_DatabaseDetails','P') <> 0
DROP PROCEDURE dbo.usp_DatabaseDetails
GO
CREATE PROCEDURE dbo.usp_DatabaseDetails (
@DBName VARCHAR(100) = NULL
)
AS
SET NOCOUNT ON;
/*
Returns database file/filegroup details for the given @DBName. Informational only, helps WHEN assessing new environment(s).

EXEC dbo.usp_DatabaseDetails @DBName = 'MINE' -- Details for the given @DBName
EXEC dbo.usp_DatabaseDetails @DBName = '*'    -- Details for all databases on the server
EXEC dbo.usp_DatabaseDetails @DBName = 'HELP' -- Ask for 'help' regarding the procedure function

Auth: Your Name
Date: XX/XX/XXX
*/
IF(@DBName IS NULL OR @DBName = 'HELP')
BEGIN   
    PRINT 'HELP for usp_DatabaseDetails'
    PRINT ''
    PRINT 'This procedure will return file/filegroup details for the given @DBName, in this format:'
    PRINT ''
    PRINT '1) Database Name'
    PRINT '2) Logical name for all the database files, mdf, ldf and ndf'
    PRINT '3) Physical SIZE of each files in megabytes (MB)'
    PRINT '4) Drive letter upon which each of file resides'
    PRINT '5) Filegroup type '
    PRINT '6) Filegroup location, with the full path'
  RETURN
END

IF(@DBName IS NULL)
BEGIN   
    SELECT
        DB_Name() [DatabaseName],
        SUBSTRING(name,1,50) [Logical Name],
        SIZE/128 [SIZE in MB],
        SUBSTRING(FileName,1,1) [Drive],
        CASE groupid WHEN 1 THEN 'Primary'
            WHEN 0 THEN 'Log' ELSE 'Secondary' END [FileGroup Type],
        SUBSTRING(FileName,1,255) [Location]
    FROM
        sysfiles
END

IF(@DBName IS NOT NULL AND @DBName <> '*')
BEGIN
    IF NOT EXISTS(SELECT name FROM master..sysdatabases WHERE name = @DBName)
    BEGIN
       IF(@DBName='HELP')
       BEGIN
           RETURN;
       END

       IF(@DBName <> 'HELP')
       BEGIN
          PRINT 'The given @DBName '''+LTRIM(@DBName)+''' is not a database on this server.'
          RETURN;
       END
   END

    EXEC('SELECT '''+@DBName+''' [DatabaseName], SUBSTRING(Name,1,50) [Logical Name],
    SIZE/128 [MB],SUBSTRING(FileName,1,1) [Drive],CASE groupid
    WHEN 1 THEN ''Primary''
    WHEN 0 THEN ''Log''
    ELSE ''Secondary''
    END [FileGroup Type],
    SUBSTRING(FileName,1,255) [Location]FROM '+@DBName+'..sysfiles')

END

IF(@DBName = '*')
BEGIN   
    EXEC sp_msforeachdb 'SELECT UPPER(''?'') [DatabaseName],SUBSTRING(Name,1,50)  
    [Logical Name],SIZE/128 [MB],SUBSTRING(FileName,1,1) [Drive],CASE groupid
    WHEN 1 THEN ''Primary''
    WHEN 0 THEN ''Log''
    ELSE ''Secondary''
    END [FileGroup Type],
    SUBSTRING(FileName,1,255) [Location]
FROM ?..sysfiles'  END
 

SET NOCOUNT OFF;