File tools

 

-- File tools 
 
       -- Find the BAK files:
       SELECT TOP 100 
              m.physical_device_name
       ,      b.backup_start_date
       ,      b.backup_finish_date
       ,      b.backup_size/1024.0 BackupSizeKB
       FROM   msdb.dbo.backupset b
       JOIN   msdb.dbo.backupmediafamily m 
              ON     b.media_set_id = m.media_set_id
       WHERE  /* database_name like '%Mysmco%' */ 
              m.physical_device_name LIKE '%bak%' 
       ORDER BY 
              b.backup_finish_date DESC
 
       -- Find the MDF's and LDF's:
       DECLARE @command varchar(1000)
       SELECT @command = 'SELECT * FROM sys.database_files'
       EXEC sp_MSforeachdb @command
 
       -- Or ... easier
       EXEC sp_MSforeachdb 'USE ? SELECT ''?'', SF.filename,  SF.size FROM sys.sysfiles SF'
 
       -- Or ... even easier
       SELECT 'USE [' + name + ']; SELECT SF.filename, SF.size FROM sys.sysfiles SF'
       FROM master.dbo.sysdatabases