Unresolved Reference To Index Despite Its Existence

Question

I'm attempting to write a stored procedure that DROPs a columnstore index, the index-creation script does exist, the SSDT table designer for the table associated with the index in question does display the correct index, and yet Visual Studio is producing "Procedure: [dbo].[TestProcedure] has an unresolved reference to object [dbo].[TestTable].[TestIndex]". The 'Build Action' setting is set to build on all three files, and all objects are in the same schema. I'm using Visual Studio Professional 2015 With Update 3 and SSDT 14.0.6 Is there any way to resolve the reference warning produced by the code below?

I tried the solution proposed in this post, but it didn't resolve the issue: SQL Server Data Tools (SSDT) warns of an unresolved table reference, but the table exists

TestTable.sql

CREATE TABLE [dbo].[TestTable]( [Id] INT NOT NULL PRIMARY KEY )

TestIndex.sql

CREATE CLUSTERED COLUMNSTORE INDEX [TestIndex] 
ON [dbo].[TestTable]

TestProcedure.sql

CREATE PROCEDURE dbo.TestProcedure
AS
DROP INDEX TestIndex ON dbo.TestTable

Answer 1

You could use dynamic SQL:

CREATE PROCEDURE dbo.TestProcedure
AS
BEGIN
  EXEC('DROP INDEX IF EXISTS TestIndex ON dbo.TestTable');
END;

Alternatively adding IF EXISTS only:

CREATE PROCEDURE dbo.TestProcedure
AS
BEGIN
  DROP INDEX IF EXISTS TestIndex ON dbo.TestTable;
END;