Unresolved Reference to Object

sql

sql-server

database-projects

intellisense

byNikita Barsukov·Mar 9, 2025

If you are encountering the "unresolved reference to object [INFORMATION_SCHEMA].[TABLES]" error, a quick fix involves setting your database context properly and confirming that you have the appropriate access permissions. Apply the following syntax:

USE YourDB; -- Replace with your database akin to swapping your face-mask to one you like
SELECT * FROM INFORMATION_SCHEMA.TABLES; -- Party time! Invite everyone from INFORMATION_SCHEMA.TABLES!

Make sure to replace YourDB with your actual database name.

If it doesn't resolve the issue, you may need to add a database reference to the master database within your development environment, especially if you are using SQL Server Data Tools or a similar IDE.

Adding the Master Database Reference

In Visual Studio:

Mouse right-click on your database project in Solution Explorer.
Navigate to Add > Database Reference.
Select the System Database radio button and highlight master.
Press OK and wait (possibly while making a coffee ☕)! Visual Studio will take a moment to refresh IntelliSense.

// Why did the developer go broke?
// Because he used up all his cache. 😄

Your stored procedures, UDFs, or Views referencing INFORMATION_SCHEMA.TABLES will execute correctly despite an annoying warning.

To alleviate the effect of these warnings on your project, you can add <TreatTSqlWarningsAsErrors>false</TreatTSqlWarningsAsErrors> in your database project file.

Deep Dive into the Issue and Solutions

Querying System Views Directly

Given that INFORMATION_SCHEMA views are SQL standard and more user-friendly, you might want to use sys.objects or related system views instead when you need deeper information or more speed. Here's a sample query:

SELECT * FROM sys.objects WHERE type='U' -- U for User Tables, B for Batman

Fixing Misconfigurations in Build Settings

Misconfigured Build Settings in your project can often lead to unresolved reference issues. Adjust the Target platform (SQL Server version) or other XML elements related to database references in the .sqlproj file.

Troubleshooting Persistent Issues

If the issue persists, these further steps may come in handy:

Remove and re-add the master database reference.
Look at deployment scenarios and build pipelines that tolerate warnings.
Consider changing the target platform to resolve compatibility issues.

Withstanding Warnings during Build and Deployment

Full build and publish despite warnings

A SQL Server database project can often be published successfully even when there are unresolved reference warnings. That said, always strive to understand and minimize warnings, ultimately ensuring predictable deployments.