How do I refresh the columns of a view in SSDT?

Question

I'm getting this error when trying to deploy a SQL Server Data Tools (SSDT) database project:

View or function 'dbo.Employees' has more column names specified than columns defined.

I deleted a column from the underlying table and the view looks like this:

CREATE VIEW [dbo].[Employees] AS SELECT * FROM [$(ExternalDB)].[dbo].[Employees];

Doing a Google search brings back this page which says that SQL Server keeps meta data on views which must be refreshed. They mention this command:

EXEC sp_refreshview 'Employees';

If I put that in the pre-deployment script, it'll run before the column was dropped. If I put it in the post-deployment script, the deployment will throw the error before it gets executed. So my question is where or how can I do that with an SSDT project?

Answer 1

This is interesting as by default ssdt will refresh any views which depend on any table that has changed as part of a deployment.

Was the column dropped as part of a normal ssdt deployment?

In your publish profile or publish options are you setting ScriptRefreshModule to false?

Aside from this select * in a view is bad practice, put the full column list and this problem disappears, you can even right click the "select *" and choose to expand to get the full column list - do that instead :) Ed