Sql-server – SSMS 2012 is not showing the tables of the system databases

msdb and model you may be able to recover data from by following the same process as you would if moving them. Use a test/dev instance for a dry run.

1. Stop SQL
2. Start with trace flag 3608 (master only recovery mode) NET START MSSQLSERVER /f /T3608
3. ALTER DATABASE msdb|model MODIFY FILE( NAME=xyz, FILENAME='filepath')
4. Stop SQL, move files in to place, restart SQL

KB914171 details one possible error that may be reported and a fix/workaround by:

use msdb
go
delete from msdb.dbo.syssubsystems
exec msdb.dbo.sp_verify_subsystems 1
go

In my mind general checkdb would really only apply to master and msdb. You shouldn't have volatile data in model or tempdb (and Kin points out a good article on corruption in tempdb - should be much less of an issue if you move to a more modern version of SQL Server). I wouldn't bother doing this routinely against tempdb, but only deal with it if you actually get reports of corruption. IMHO. If you've added user objects to model so that they are created in every new database, that might increase the risk there so you may want to include it. Typically it is quite small so it doesn't really hurt to include it anyway.

For defrag, MSDB is really the only database you should worry about here (especially if your backup history and job history tables grow unchecked). Master would be a candidate if you put user tables there, but I would rather fix that by removing them altogether than to try to optimize them. As above, you shouldn't have frequently changing data in model, and tempdb should not have any permanent objects either, so defragging there is either wasteful or spurious or both.