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

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
GOCREATE 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;