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

Comments

• Anonymous
  June 10, 2009
  PingBack from http://tune-up-pc.com/blog/?p=2261

• Anonymous
  June 10, 2009
  is there a similar option for including system views? i.e. sys.tables, etc.?

• Anonymous
  June 15, 2009
  Q: is there a similar option for including system views? i.e. sys.tables, etc.? A: You need to add a reference to the master.dbschema file or other dbschema file depending where the objects are defined. Almost all documented objects are defined in the master.dbschema file.

• Anonymous
  July 21, 2009
  Introducing such a topic you'd like to congratulate you've let us know. Have good work

• Anonymous
  April 26, 2010
  I attempted to follow the instructions above and ran into an issue. I currently using a reference for master dbschema and when i attempt to add it I get the error that the project already has a reference to the schema file. Is there something else I can try?

• Anonymous
  April 26, 2010
  As part of normal processing, I check for the existence of temp tables prior to creating them. So there is a check if exists (select * from tempdb.sys.sysobjects where name like '##names%' and type = 'u')  to determine if table exist before issuing the drop. I get 3 warning generated from this: for sys.sysobjects     sys.sysobjects.type     sys.sysobjects.name

• Anonymous
  April 27, 2010
  I copied the master.dbschema file, changed the literals in the file from master to tempdb. My one solution contains mutliple projects, currenty I have no errors and a total of 69 warnings: Then I added it in one project which already had master.dbscheam as a reference.  The few tempdb warning went away. Still no errors and the number of warning went down to 57. So we resolved 12 warnings dealing with tempdb in this project. I added it in one project that also had master.dbschema specified, this time i went from no errors to 81 errors plus 133 warnings below is an example of the one of the errors that I received: Error 14 SQL03006: View: [dbo].[tablecolumns] has an unresolved reference to object [dbo].[syscolumns]. E:projectsDeltaNetSiteJun10DeltanetDbSchema ObjectsViewsdbo.tablecolumns.view.sql 4 14 DeltanetDb I then removed the tempdb.dbschema reference and rebuilt the projects, I now have 79 errors and 135 warnings. I then removed the master.dbschema reference and rebuilt the projects. Now I have 130 errors with 84 warnings. Then I added back the master.dbschema file and rebuilt the projects and now I am back to no errors and 57 warnings. Part of the tempdb warnings is there is code to check for existence of tmep table so they can be dropped prior to being created. The exmaple below generates three warning messages. one for the sysobjects table, one each for both the name and the type columns. Example. IF EXISTS(select * from tempdb.sys.sysobjects where type = 'u' and name like '##bookmods%') BEGIN DROP  TABLE ##bookmods END Do you have any ideas for me? Also we have created and installed software that place custom procedures in master database. Is there a way to include them in the master.dbschema to resolve the warnings? I also have a simuliar issue with custom  objects in msdb.

• Anonymous
  April 27, 2010
  I copied the master.dbschema file, changed the literals in the file from master to tempdb. My one solution contains mutliple projects, currenty I have no errors and a total of 69 warnings: Then I added it in one project which already had master.dbscheam as a reference.  The few tempdb warning went away. Still no errors and the number of warning went down to 57. So we resolved 12 warnings dealing with tempdb in this project. I added it in one project that also had master.dbschema specified, this time i went from no errors to 81 errors plus 133 warnings below is an example of the one of the errors that I received: Error 14 SQL03006: View: [dbo].[tablecolumns] has an unresolved reference to object [dbo].[syscolumns]. E:projectsDeltaNetSiteJun10DeltanetDbSchema ObjectsViewsdbo.tablecolumns.view.sql 4 14 DeltanetDb I then removed the tempdb.dbschema reference and rebuilt the projects, I now have 79 errors and 135 warnings. I then removed the master.dbschema reference and rebuilt the projects. Now I have 130 errors with 84 warnings. Then I added back the master.dbschema file and rebuilt the projects and now I am back to no errors and 57 warnings. Part of the tempdb warnings is there is code to check for existence of tmep table so they can be dropped prior to being created. The exmaple below generates three warning messages. one for the sysobjects table, one each for both the name and the type columns. Example. IF EXISTS(select * from tempdb.sys.sysobjects where type = 'u' and name like '##bookmods%') BEGIN DROP  TABLE ##bookmods END Do you have any ideas for me? Also we have created and installed software that place custom procedures in master database. Is there a way to include them in the master.dbschema to resolve the warnings? I also have a simuliar issue with custom  objects in msdb.

• Anonymous
  April 29, 2010
  Dear Gert, I have followed your instructions, however it seems that when including both tempdb and master references in this way - any local references to system views (sys.tables etc) then come up as unresolved. I guess that the master dbschema has something inside it that tells the system to include some of the views globally (i.e. in all projects) which is the only way that I can see that adding just master solves any local system views (i.e there is no 3 part name, so it shouldn't look in database references normally). So following this logic by copying master.dbschema and including it effectively twice, it then creates unresolved references. Prove this by creating a new project, add a sp that does a select * from sys.databases then add both master.dbschema & tempdb.dbschema - you will find it errors. Take out the tempdb.dbschema and it doesn't. Similar to iamtig I need to refer to tempdb.sys.tables to get details on temporary tables. Can anyone suggest how to deal with this properly? Possibly by refining Gert's method above?

• Anonymous
  April 29, 2010
  Further to my previous post this doesn't work if you need master.dbschema reference in your project also (at least in vs2010) The solution is to create a cut down version of the master and then find/replace <Annotation Type="GloballyScoped"/> to remove the globally scoped nature of the remaining objects. Then you don't have clashing objects at global level