Search code examples
sql-server-data-tools

reference system objects in SSDT Projects

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.

Solution

  • 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:

    ssdt add reference page

    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 :) )