Suppose I have created a new database in SQL Server with a file group FG1 and marked this as the default. All my user tables etc are now created in FG1. However I also created a file that is part of the PRIMARY file group. Apparently the system objects are stored in this primary file. I was wondering
What kind of system objects are these? Can I inspect them? Do they appear in the 'System' Database?
Apparently there is some reliability gain to doing things this way. Suppose the file in my primary file group got corrupted, can I still recover my database?
Best Answer
The system objects are the objects which store the schema for all your objects. The stored procedures, table schemas, etc. are all stored in these system objects.
If the system objects become corrupt you have to restore the primary filegroup.
You can view the data within the system objects by querying the system catalog views such as sys.tables, sys.procedures, etc.
Not sure what you are talking about, there's no database called "System". The system databases are master, msdb, model (to some extent) and tempdb. There is no user database data stored within the system databases. (Things like logins, jobs, SSIS packages not withstanding.)