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

If you ever encounter this SQL Server error when selecting from a view then somebody has probably dropped columns from the base table. Here’s a quick run through of the problem.

CREATE TABLE dbo.Contact
(
	Id INTEGER IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
	FirstName VARCHAR(50) NOT NULL,
	LastName VARCHAR(50) NOT NULL,
	DOB DATETIME NOT NULL,
	Phone VARCHAR(30) NULL,
	Email VARCHAR(200) NULL,
	Mobile VARCHAR(20) NULL,
	Website VARCHAR(100) NULL
);

Insert a test record.

INSERT INTO dbo.Contacts
(
	FirstName,
	LastName,
	DOB,
	Email,
	Website
)
VALUES
(
	'Rhys',
	'Campbell',
	'01-Jun-80',
	'noone@tempinbox.com',
	'http://www.youdidwhatwithtsql.com'
);

Create a view on this table.

CREATE VIEW vw_Contacts
AS
	SELECT *
	FROM dbo.Contacts;

Verify the view is functional

SELECT *
FROM vw_Contacts;

Now drop a column from the Contacts table.

ALTER TABLE dbo.Contacts DROP COLUMN Phone;

Now try selecting from the view again.

SELECT *
FROM vw_Contacts;

So what’s going on here? The view is expecting the Phone column to still be in the Contact table. We created our view with an asterisk so this shouldn’t matter right? Uvavu! SQL Server stores metadata about the view when you create it. Changes to underlying tables can cause issues. Luckily the fix is easy.

EXEC sp_refreshview 'vw_Contacts';

SELECT *
FROM vw_Contacts;

This procedure will update the metadata stored by the view making it functional again. Presumably this is akin to dropping and recreating the view. This is a fairly trivial example but in a large system, with lots of views, and lots of developers, this could cause big headaches. I recommend you read the MSDN page for sp_refreshview. There’s a couple of useful scripts in the comments section for making light work of this.