Recently, I was called upon by one of the teams in our organization who were attempting to rename a column and had ended up with a weird error when refreshing a view based on the modified table. The error that they were facing was similar to:

Msg 207, Level 16, State 1, Procedure sp_refreshsqlmodule_internal, Line 71
Invalid column name ‘Vendor’.

They had already attempted to refresh the view definition using the sp_refreshview system stored procedure as mentioned in one of my previous posts on refreshing the view metadata for non schema-bound view.

As I finished my investigations with them I realized that their view, although created without SCHEMABINDING, was created with the best practices of having a clearly defined column listing. Changing the names of the underlying columns (or even dropping any column) therefore requires us to drop and recreate the view.

Such views should be evaluated for use of the SCHEMABINDING clause. The SCHEMABINDING clause will prevent the changes to the underlying table because SQL Server would be able to detect that dependent objects will be affected by the change.

Here’s a quick example of how to reproduce the error on your box, and the affect of SCHEMABINDING:

USE tempdb;
GO

/************** STEP 0 - SAFETY CHECK **************/
--Safety Check
IF OBJECT_ID('dbo.vProductList','V') IS NOT NULL
    DROP VIEW dbo.vProductList;
GO

IF OBJECT_ID('dbo.ProductList','U') IS NOT NULL
    DROP TABLE dbo.ProductList;
GO

/************** STEP 1 - CREATE TEST TABLE, VIEW AND DATA **************/
--Create the table
CREATE TABLE dbo.ProductList (ProductId INT IDENTITY(1,1),
                              Vendor VARCHAR(50),
                              ProductName VARCHAR(50),
                              CONSTRAINT pk_ProductId PRIMARY KEY CLUSTERED (ProductId)
                             );
GO

--Create the view
CREATE VIEW vProductList AS
SELECT pl.ProductId,
       pl.Vendor,
       pl.ProductName
FROM dbo.ProductList AS pl;
GO

--Insert some test data
INSERT INTO dbo.ProductList (Vendor, ProductName)
VALUES ('Microsoft', 'Windows'),
       ('Microsoft', 'SQL Server'),
       ('Microsoft', 'BizTalk')
GO

/************** STEP 2 - Rename the column and attempt to refresh the view **************/
EXEC sp_rename @objname = 'dbo.ProductList.Vendor', @newname = 'VendorName', @objtype = 'COLUMN';
GO

SELECT * FROM dbo.ProductList;
GO

--Now, attempt to refresh the view
EXEC sp_refreshview 'dbo.vProductList';
GO

/************** STEP 3 - Recover from the error **************/
--Recreate the view
IF OBJECT_ID('dbo.vProductList','V') IS NOT NULL
    DROP VIEW dbo.vProductList;
GO

--Create the view
CREATE VIEW vProductList AS
SELECT pl.ProductId,
       pl.VendorName,
       pl.ProductName
FROM dbo.ProductList AS pl;
GO

--SELECT from the view
SELECT * FROM dbo.vProductList;
GO

/************** STEP 4 - Affect of SCHEMABINDING **************/
--Recreate the view
IF OBJECT_ID('dbo.vProductList','V') IS NOT NULL
    DROP VIEW dbo.vProductList;
GO

--Create the view
CREATE VIEW vProductList 
WITH SCHEMABINDING 
AS
SELECT pl.ProductId,
       pl.VendorName,
       pl.ProductName
FROM dbo.ProductList AS pl;
GO

--SELECT from the view
SELECT * FROM dbo.vProductList;
GO

--Now, let us attempt to rename the column in the base table
EXEC sp_rename @objname = 'dbo.ProductList.VendorName', @newname = 'Vendor', @objtype = 'COLUMN';
GO

/************** STEP 99 - CLEANUP **************/
--Cleanup
IF OBJECT_ID('dbo.vProductList','V') IS NOT NULL
    DROP VIEW dbo.vProductList;
GO

IF OBJECT_ID('dbo.ProductList','U') IS NOT NULL
    DROP TABLE dbo.ProductList;
GO

When SCHEMABINDING is used, SQL Server generates the following error when we attempt to alter the base table:

Msg 15336, Level 16, State 1, Procedure sp_rename, Line 497

Object ‘dbo.ProductList.VendorName’ cannot be renamed because the object participates in enforced dependencies.

SCHEMABINDING therefore helps to prevent breaking any dependent objects as a result of a base object change.

Further Reading

• Refreshing the view metadata for non schema-bound view [Link]
• sp_refreshview [Books On Line Link]

Until we meet next time,

Be courteous. Drive responsibly.