System Objects in TEMPDB

Today a user send me a question how to resolve reference of system objects inside tempdb?

My first thought was this should get resolved by loading the master.dbschema file. However this did not resolve the issue, so time to ask for an example, which slightly altered looks something like this:

    1:  CREATE PROC [dbo].[testproc]

    2:  AS

    3:      SET NOCOUNT ON

    4:      -- code simplified for this example    

    5:      SELECT [object_id],

    6:                   [name]

    7:      FROM   [tempdb].[sys].[objects] 

When you add the stored procedure above to a project you will get the following 3 warnings:

When you add a reference to the master.dbschema file, found in:

%ProgramFiles%\Microsoft Visual Studio 9.0\VSTSDB\Extensions\SqlServer\<200?>\DBSchemas\master.dbschema

You will get the following 3 warnings, can you spot the difference or improvements?

Very good, there are no differences, this did not resolve anything.

The reason being that you are referencing a 3-part name, where the database name is not master. So the only way to resolve this issue is to change the database reference literal from master to tempdb. When you are adding the database reference to the master.dbschema file, it defaults to use "master” as the literal database variable.

You need to change the database literal variable from master in to tempdb. And then it will resolve the 3-part names to tempdb.sys.objects in this example.

After making this change the warnings are gone, as you can see in the error list.

You can always validate you database reference settings by double clicking on the reference inside Solution Explorer.

If you have to resort to using this trick, I advice you to copy the master.dbschema to tempdb.dbschema and optionally purge the objects in the dbschema that you do not need in order to speed up the loading of you project.

GertD @ www.DBProj.com