reference system objects in SSDT Projects

Question

I have a SQL Server project in VS and SSDT, and am scripting out a stored procedure that has to reference system objects (sys.tables, sys.all_columns, etc.). But when I build the project I get tons of warnings that the procedure "has an unresolved reference to [sys].[tables]"

Is there a way to get system objects in a reference so as to avoid this? Seems like if the Project Properties has a Target Platform of any recent SQL version, then it should KNOW that these system objects already exist inherently.

It doesn't stop the build, but makes looking for TRUE warnings, ones that need to be addressed, a little more difficult.

Any suggestion is appreciated. Thanks.

Tynishatynwald

Answer 1

References are added in a similar way to standard references in Visual Studio: right click on the “References” node of your SSDT project and choose “Add Database Reference”, this will start the “Add Database Reference” dialog:

Choose the "System" database reference and it should stop the warnings about objects that exist in master.

(The text and image are copied from an article I wrote: https://www.red-gate.com/simple-talk/sql/sql-tools/sql-server-data-tools-ssdt-and-database-references/) (so technically it is plagurism but by me of me :) )

Lumen

I'll give this credit for a correct answer. To be honest, I didn't think that system catalog views (sys.tables, etc.) were all contained within the master database. Adding a reference to it for a project that has a target platform of SQL Server 2016 did in fact remove the warnings. However, the same is not true if the Target Platform is "Microsoft Azure SQL Database V12". Any ideas on this point? – Tynishatynwald

Which objects are you referencing that aren’t in the azure v12 master dacpac? – Lumen

[sys].[tables], [sys].[all_columns], [sys].[schemas] Basic system views. – Tynishatynwald

something isn't right then because those views are definitetly in the azure v12 master dacpac, try setting the project to azure v12 then removing and re-adding the reference to master using the second section "System Database". This is a sample project I built that references sys.tables and azure v12 and I don't get any warnings, does this work for you? github.com/GoEddie/testProjectFor55150224 – Lumen

I am using latest Visual Studio 2017 version XX on Windows 10. – Tynishatynwald

I downloaded your git code. It failed a BUILD but that was because the master.dacpac it referenced was in the \Community\ folder and mine was in \Enterprise\. I deleted the reference, re-added, and the BUILD succeeded and there were no warnings. But my original project, referencing the same master.dacpac, still has warnings. So it must be a Project setting somewhere? – Tynishatynwald

sorry i'm not sure, maybe try deleting re-adding, doing a clean etc I don't think there is a setting other than the project version (DSP) that can affect this – Lumen

I figured it out, and boy do I feel stupid! I had TWO SQL projects that each had references to [sys].[tables] etc. I wasn't paying attention to the Project column title in the Error List pane. One project had a reference to master, the other, alas, did not. Ed: Thanks for your help and patience! – Tynishatynwald

Phew, I honestly was stumped :) – Lumen