SupportSQL

Very useful in identifying all kinds of valuable database statistics and information, e.g. non-standard settings, large files, incorrect recovery modes, database files located on system drive, etc.

CREATE PROCEDURE [dbo].[gp_DBA_DatabaseStats]
AS 
IF NOT EXISTS ( SELECT
                    *
                FROM
                    sys.objects
                WHERE
                    object_id = object_id(N'[dbo].[DBStats]')
                    AND type IN (N'U') ) 
    BEGIN 





        CREATE TABLE [dbo].[DBStats]
            (
             [ServerName] [varchar](100) NULL
            ,[DatabaseName] [varchar](100) NULL
            ,[FileSizeMB] [int] NULL
            ,[LogicalFileName] [sysname] NOT NULL
            ,[PhysicalFileName] [nvarchar](520) NULL
            ,[Status] [sysname] NOT NULL
            ,[Updateability] [sysname] NOT NULL
            ,[RecoveryMode] [sysname] NOT NULL
            ,[FreeSpaceMB] [int] NULL
            ,[FreeSpacePct] [varchar](7) NULL
            ,[FreeSpacePages] [int] NULL
            ,[PollDate] [datetime] NULL)
        ON  [PRIMARY] 





    END 





DELETE
    dbo.DBStats 





DECLARE @command varchar(5000) 





SELECT
    @command = 'Use [' + '?' + '] SELECT 





@@servername as ServerName, 





' + '''' + '?' + '''' + ' AS DatabaseName, 





CAST(sysfiles.size/128.0 AS int) AS FileSize, 





sysfiles.name AS LogicalFileName, sysfiles.filename AS PhysicalFileName, 





CONVERT(sysname,DatabasePropertyEx(''?'',''Status'')) AS Status, 





CONVERT(sysname,DatabasePropertyEx(''?'',''Updateability'')) AS Updateability, 





CONVERT(sysname,DatabasePropertyEx(''?'',''Recovery'')) AS RecoveryMode, 





CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, ' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB, 





CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name, 





' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(sysfiles.size/128.0)) 





AS decimal(4,2))) AS varchar(8)) + ' + '''' + '%' + '''' + ' AS FreeSpacePct, 





GETDATE() as PollDate FROM dbo.sysfiles' 





INSERT INTO
    dbo.DBStats
    (
     ServerName
    ,DatabaseName
    ,FileSizeMB
    ,LogicalFileName
    ,PhysicalFileName
    ,[Status]
    ,Updateability
    ,RecoveryMode
    ,FreeSpaceMB
    ,FreeSpacePct
    ,PollDate)
    EXEC sp_MSForEachDB @command --for all server DBs 





SELECT
    ServerName
   ,DatabaseName
   ,FileSizeMB
   ,LogicalFileName
   ,PhysicalFileName
   ,[Status]
   ,Updateability
   ,RecoveryMode
   ,FreeSpaceMB
   ,FreeSpacePct
   ,PollDate
FROM
    dbo.DBStats
ORDER BY
    ServerName
   ,DatabaseName 

—by Alek Kirstein – SupportSQL.com