Please visit SQLVERSITY.COM for more details.
As its name implies, it is a master of SQL Server. Without this database we cannot start the SQL Server at all. It stores the information of all databases and their objects, logins, users, configuration settings etc…
Note: It is better to take the back up of this database frequently. Because if you lose this database data, you will lose everything. Of course we can repair this database, but we can’t get the existing data back 😦
It is used as the template for all databases created on an instance of SQL Server. If you create a new database, then the contents of this database will be copied to the newly created database. If you want to add some more objects to the newly created databases, then create those objects in Model database. So that those objects will be created automatically whenever you create a new database.
It stores the data regarding DTS packages, SSIS packages, SQL Server agent jobs, database backups, database mails, service broker etc… If you are not working in the above, you can ignore this database.
As the name suggests, it is a temporary database. It stores the temporary objects like global temporary table, local temporary tables etc… This database exists till you disconnect the connection. When the server starts, again it will be re-created as per the Model database template.
Meta data is data about data. i.e. This data describes other data. It describes when and how and by whom a particular data was created, updated etc… For example, if you take data in a table, the meta data will be when the table was created, who created the table, who inserted the data, when they inserted, in which schema it was created, in which database it was created, who have permissions to access it etc…