Información util acerca de las bases de datos

Este es mi script para ver informacion acerca de todas las bases de datos disponibles en la instancia del SQL Server
- Bases de datos
- id
- Nombre
- Tamaño
- Disco en el que esta alojado el archivo de datos y log
- tamaños de los archivos de datos y log
- Espacio libre en disco
- Porcentaje de utilización del archivo
- Valores de Auto growth
- Recovery Model
- Compativility Level
- Ultimo backup (Full y Transacion Log)
- Ultimo restore

Archivo Principal

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

SET NOCOUNT ON

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;

--select * from #HDB

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 REC_MODEL,

CASE B.TYPE

WHEN 0 THEN A.compatibility_level

ELSE NULL

END AS COMP_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]

FROM SYS.DATABASES A

JOIN SYS.MASTER_FILES B

ON A.DATABASE_ID = B.DATABASE_ID

JOIN #TMPFIXEDDRIVES C

ON LEFT(B.PHYSICAL_NAME,1) = C.DRIVE

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







--WHERE A.NAME <> 'model'

ORDER BY DATABASENAME



DROP TABLE #TMPFIXEDDRIVES

DROP TABLE #TMPSPACEUSED

DROP TABLE #HDB