Databases Info

Useful information about all the databases

- Name

- ID

- Size

- Files Disk and Path

- Files Sizes

- Free disk space

- Files Percentage of usage

- Files Auto growth Settings

- Recovery model

- Compatibility Level

- Last Backup (Full backup and transaction log backup)

- Last Restore

- Database Mirroring State and Status

-- MAIN SCRIPT

SET NOCOUNT ON

IF (object_id( 'tempdb..#TMPFIXEDDRIVES' ) IS NOT NULL) DROP TABLE #TMPFIXEDDRIVES

IF (object_id( 'tempdb..#TMPSPACEUSED' ) IS NOT NULL) DROP TABLE #TMPSPACEUSED

IF (object_id( 'tempdb..#HDB' ) IS NOT NULL) DROP TABLE #HDB

CREATE TABLE #TMPFIXEDDRIVES (

DRIVE CHAR(1),

MBFREE INT)

INSERT INTO #TMPFIXEDDRIVES

EXEC xp_FIXEDDRIVES

CREATE TABLE #TMPSPACEUSED (

DBNAME VARCHAR(255),

FILEID INT,

FILENME VARCHAR(255),

SPACEUSED FLOAT)

--// SP_HELPDB

CREATE TABLE #HDB (

name sysname not null,

db_size varchar(25) not null,

owner varchar(40) not null,

dbid int not null,

created smalldatetime not null,

status varchar(500) not null,

compatibility_level int not null

)

INSERT INTO #HDB

exec sp_helpdb;

INSERT INTO #TMPSPACEUSED

EXEC( 'sp_msforeachdb''use [?]; Select ''''?'''' DBName,fileid, Name FileNme, fileproperty(Name,''''SpaceUsed'''') SpaceUsed from sysfiles''')

SELECT

      @@servername as servername,

      A.Database_id as DBid,

      A.NAME AS DATABASENAME

      ,CASE D.FILEID

      WHEN 1 THEN ltrim(XX.db_size)

      ELSE NULL

      END as db_size

      ,CASE D.FILEID

      WHEN 1 THEN XX.owner

      ELSE NULL

      END as owner

      ,CASE D.FILEID

      WHEN 1 THEN XX.created

      ELSE NULL

      END as created

      ,C.DRIVE,

      C.MBFREE AS DISKFREE_MB,

      D.FILEID as FileId,

      B.NAME AS FILENAME,

      CASE B.TYPE

      WHEN 0 THEN 'DATA'

      ELSE TYPE_DESC

      END AS FILETYPE,

      (B.SIZE * 8 / 1024)AS FILESIZE_MB,

      ROUND((B.SIZE * 8 / 1024) - (D.SPACEUSED / 128),2) as SPACEFREE_MB,

      ROUND(100-((((B.SIZE * 8 / 1024) - (D.SPACEUSED / 128))*100)/

      CASE(B.SIZE * 8 / 1024)

      WHEN 0 THEN 1

      ELSE (B.SIZE * 8 / 1024)

      END

      ),2) as [%USED],

      b.size,

      b.max_size,

      b.growth,

      b.is_percent_growth,

      --CASE (B.SIZE * 8 / 1024)

      --WHEN 0 THEN 1 ELSE 0 END as VALID,

      B.PHYSICAL_NAME,

      CASE B.TYPE

      WHEN 0 THEN A.recovery_model_desc

      ELSE NULL

      END AS [Recovery_Model],

      CASE B.TYPE

      WHEN 0 THEN A.compatibility_level

      ELSE NULL

      END AS [Compatibility_Level]

      ,CASE D.FILEID

      WHEN 1 THEN BR.last_backup_finish_date

      ELSE NULL

      END as [Backup]

      ,CASE D.FILEID

      WHEN 1 THEN BR.last_TRLog_backup_finish_date

      ELSE NULL

      END as TRBackup

      ,CASE D.FILEID

      WHEN 1 THEN BR.last_restore_date

      ELSE NULL

      END as [Restore],

      DM.mirroring_role_desc+'('+DM.mirroring_state_desc+')' as DBMirror_Info

FROM SYS.DATABASES A

INNER JOIN SYS.MASTER_FILES B ON A.DATABASE_ID = B.DATABASE_ID

INNER JOIN #TMPFIXEDDRIVES C ON LEFT(B.PHYSICAL_NAME,1) = C.DRIVE

INNER JOIN #TMPSPACEUSED D ON A.NAME = D.DBNAME AND B.NAME = D.FILENME

INNER JOIN #HDB XX on XX.dbid= A.Database_id

INNER JOIN

(

SELECT D.database_id,B.last_backup_finish_date,TR.last_TRLog_backup_finish_date,R.last_restore_date

FROM sys.databases D

LEFT JOIN (

      SELECT

      BS.database_name

      ,max(BS.backup_finish_date) as last_backup_finish_date

      FROM

      msdb.dbo.backupset BS (NOLOCK)

      INNER JOIN

      msdb.dbo.backupmediafamily MF(NOLOCK) ON BS.media_set_id = MF.media_set_id

      WHERE

      BS.backup_start_date >= CAST(CONVERT(varchar(10),dateadd(mm,-3,getdate()),120) AS datetime)

      AND BS.server_name = @@servername and BS.type='D'

      GROUP BY

      BS.database_name

      ) B on D.name=B.database_name

LEFT JOIN (

      SELECT

      BS.database_name

      ,max(BS.backup_finish_date) as last_TRLog_backup_finish_date

      FROM

      msdb.dbo.backupset BS (NOLOCK)

      INNER JOIN

      msdb.dbo.backupmediafamily MF(NOLOCK) ON BS.media_set_id = MF.media_set_id

      WHERE

      BS.backup_start_date >= CAST(CONVERT(varchar(10),dateadd(mm,-1,getdate()),120) AS datetime)

      AND BS.server_name = @@servername and BS.type='L'

      GROUP BY

      BS.database_name

      ) TR on D.name=TR.database_name

LEFT JOIN (

      SELECT

      rh.destination_database_name,

      max(rh.restore_date) as last_restore_date

      FROM

      msdb.dbo.restorehistory rh (NOLOCK)

      INNER JOIN msdb.dbo.backupset BS (NOLOCK) ON rh.backup_set_id=BS.backup_set_id

      WHERE

      BS.type= 'D' AND

      RH.restore_date >=CAST(CONVERT(varchar(10),dateadd(mm,-3,getdate()),120) AS datetime)

      GROUP BY

      rh.destination_database_name

      ) R on D.name=R.destination_database_name

      ) BR on A.Database_id=BR.database_id

LEFT JOIN

            msdb.sys.database_mirroring dm (nolock) on A.database_id=dm.database_id

--WHERE A.NAME <> 'model'

ORDER BY DATABASENAME

IF (object_id( 'tempdb..#TMPFIXEDDRIVES' ) IS NOT NULL) DROP TABLE #TMPFIXEDDRIVES

IF (object_id( 'tempdb..#TMPSPACEUSED' ) IS NOT NULL) DROP TABLE #TMPSPACEUSED

IF (object_id( 'tempdb..#HDB' ) IS NOT NULL) DROP TABLE #HDB