Unresolved object reference [INFORMATION_SCHEMA]. [TABLES]

Question

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

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

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

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

I can still publish the database project without any problems, and the UDF seems to work correctly. IntelliSense fills in the view name for me, so it has no problem with it.

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

How can I resolve this warning?

Answer 1

Add the database link to master :

• Within the project, right-click the link.
• Select Add Database Link ....
• Select a system database.
• Make sure the wizard is selected.
• Click OK.

Please note that updating the VS may take some time.

Answer 2

what Sam said is the best way to do this.
However, if you have the script needed to deploy dacpac from a machine that does not have this link in this particular place, you may run into problems. Another way is to open the .project file and make sure that the next tag is false for the configuration of the assembly you are trying to run.

 <TreatTSqlWarningsAsErrors>false</TreatTSqlWarningsAsErrors> 

Thus, you do not need to add a link to your project.

Answer 3

In our project, we already have a link to master, but we had this problem. Here is the error we received:

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

To fix the reference error, in the table sql file, right-click and make sure that the BuildSettings parameter is set to Build.

Changing its build fixed it.