Few days ago, i got the chance to work with one of our legacy application. There was some changes in business requirement and i need to drop few columns from the table and so on and that table was used in one sql server view. After done my changes in table when i test my view, surprisingly it gives me an error! whooa!
How is it possible as i have used “Select *” in my view. It should automatically reflect the changes in my view. Well, SQL server will not work in that way. After referring MSDN, i knew that metadata for my view is not automatically updated when i alter any dependent tables. So in today’s post we’ll see how we can resolve this type of error in SQL Server. Alright, let’s create this scenario.
CREATE TABLE [dbo].[EmployeeMaster]( [Id] [int] IDENTITY(1,1) NOT NULL, [FirstName] [varchar](40) NOT NULL, [LastName] [varchar](40) NOT NULL, [JoiningDate] [date] NULL, [BloodGroup] [varchar](5) NULL, [PANNo] [varchar](20) NULL ) GO INSERT INTO EmployeeMaster(FirstName,LastName,JoiningDate,BloodGroup) VALUES ('Azim', 'Premji', '2015-04-27', 'B+'), ('Narayana', 'Moorthy', '2015-04-27', 'A+') GO
Now let’s create the view.
CREATE VIEW view_Employee AS SELECT * FROM dbo.EmployeeMaster
If you querying the view, everything works fine as expected. Now let us drop the “PANNo” column from the EmployeeMaster table.
ALTER TABLE EmployeeMaster DROP COLUMN PANNo
And let’s querying our view and see what happen.
We get the error! Now to fix this error one of the way is alter our view or use the sp_refreshview stored procedure. So let’s see how we can fix the error using sp_refreshview SP.
EXEC SP_REFRESHVIEW view_Employee
Now again querying our view and as expected the error is gone now. Same way let say if you have added 2 new columns in your table then this time you won’t get any error when you querying your view but those 2 new columns not reflected in your view. So again to fix this issue you need to use “sp_refreshview” as shown above.
That’s it guys. Hope you like and enjoyed this post.