In the March edition of MSDN we provided an overview of Server Projects and how to reference the master.dbschema file to resolve references to system objects. If you have a project that references the master.dbschema file, you may have noticed it takes a while to establish this reference. You may have also noticed the project may not load or deploy as quickly as it did before referencing the master.dbschema file. This is because when referencing the master.dbschema file you are adding all the system objects found in a typical SQL Server instance to your database model in Visual Studio. The number of system objects defined in the master.dbschema file greatly outnumbers the user created objects for many database projects. If you have multiple projects referencing the master.dbschema file this can be compounded as each projects reference to the master.dbschema has its own copy of the database model in memory.
Master dbschema files do not contain objects you will deploy. Their primary purpose is to resolve references to system objects and system catalogs. The dbschema file is actually a serialized version of the master database schema model. It is an xml file that can be customized if you like. Suppose you have a project that contains just a few stored procedures that reference an extended stored procedure. You could reference one of the static master.dbschema files provided with the project, but that is a fair amount of overhead for just one extended stored procedure. You could also just suppress the warning to ignore the unresolved reference to the extended stored procedure, but this might not be ideal as you plan to work with the extended stored procedure in many of your stored procedures and want it to be resolved. Alternatively, you can customize your master.dbschema file to have only the system objects that are approved for referencing by the database development team.
To customize the master dbschema file you simply remove the elements you do not need or reference in your project. Let's assume that our project only references the extended stored procedures that deal with extended properties. These include fn_listextendedproperty, sp_addextendedproperty, sp_dropextendedproperty and sp_updateextendedproperty. You want to customize the master.dbschema file to include only these objects.
To accomplish this you:
Establishing the reference to your customized master.dbschema now only takes seconds. Loading your project should also be a little quicker now that the full model of the master.dbschema is not loaded each time for each referencing database project. In this example we have taken the master.dbschema file down from 8 MBs to 20k. This is roughly 2000 database objects down to the 4 we selected for our example.
Customized master.dbschema file in VS Editor
The customized master.dbschema from the example is attached.