unresolved reference to object [INFORMATION_SCHEMA].[TABLES]

Question

I've created a UDF that accesses the [INFORMATION_SCHEMA].[TABLES] view:

CREATE FUNCTION [dbo].[CountTables]
(
    @name sysname
)
RETURNS INT
AS
BEGIN
    RETURN
    (
        SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @name
    );
END

Within Visual Studio, the schema and name for the view are both marked with a warning:

SQL71502: Function: [dbo].[CountTables] has an unresolved reference to object [INFORMATION_SCHEMA].[TABLES].

I can still publish the database project without any problems, and the UDF does seem to run correctly. IntelliSense populates the name of the view for me, so it doesn't seem to have a problem with it.

I also tried changing the implementation to use sys.objects instead of this view, but I was given the same warning for this view as well.

How can I resolve this warning?

Resa

Answer 1

Add a database reference to master:

1. Under the project, right-click References.
2. Select Add database reference....
3. Select System database.
4. Ensure master is selected.
5. Press OK.

Note that it might take a while for VS to update.

Resa

And just a warning - if anyone has those SSDT installed in a different path, you might have issues. We ran into that in our last project where some had an E: drive and others just a C: drive. We copied that reference file out and put it in a shared location so we could reference it via .\SharedSchemas\master.dacpac. That's probably resolved now, but was an issue in the first release. – Lhary

Peter Schott has a point here. It is generally a good idea to copy referenced dacpac files somewhere inside the solution and use from there. – Catharina

For me it says "A reference to this system database already exists in your project." I'm stuck now! – Obliquity

Just for the next person to save some sanity - believe it or not even quitting VS was not enough to get this to stick - I had to reboot and then the setting started working. You may also wish to open the Visual Studio Options... SQL Server Tools... Online Editing... enable "Resolve references to system views and master database objects". – Panek

I'm having the same problem under Visual Studio 2017 and I do not see "DataBase reference" under the reference list – Ciliate

@Ciliate for those who are using VS 2017: expand your database project, right click on the References, you'll get Add database reference menu item here. – Enrollee

If the master reference already exists, I suggest removing it and following @Sam's steps to re-add the reference to master. This worked for me when my project couldn't locate the sys.sp_sqlexec reference. – Slapbang

Answer 2

In our project, we already have a reference to master, but we had this issue. Here was the error we got:

SQL71502: Procedure: [Schema].[StoredProc1] has an unresolved reference to object [Schema].[Table1].[Property1].

To resolve the reference error, on the table sql file, right click properties and verify the BuildSettings are set to Build.

Changing it build fixed it.

Gretchen

In VS 2019, I had this issue with a database project which had originally been built in 2017. To fix it, I had to remove and re-add the reference to "master" – Pussy

Answer 3

what Sam said is the best way for doing this.
However, if you have a scenario that you need to deploy the dacpac from a machine that doesn't have that reference in that specific location, you may get into trouble. Another way is to open your .project file and make sure the following tag has the value of false for the build configuration you are trying to run.

<TreatTSqlWarningsAsErrors>false</TreatTSqlWarningsAsErrors>

This way you don't need to add a reference to your project.

Worcester

This should be a last resort, because you lose feedback on sql errors! – Nidify

Answer 4

I'm using VS 2019, And even after adding the master db reference still got this issue. Resolved this by Changing the target platform of the DB project as shown in the image below. I had to remove and add back the master db again after this change.

Crookes