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
