Table is of the frequently used objects in any database. I don not remember seeing any database without a table in my career :). It allows us to keep data inside it and can manipulate it later by using views, procedure & functions etc. However, we should be very careful while customizing a table because sometimes other objects are dependable on a table and can give consistency issues as well.
Today, I came across a similar issue with one of my clients. We customized one table for them and they complained that these customizations are not reflecting in the view which is the reference table. Strange ? (Not exactly)
Let me explain the problem and its solution step by step.
Step 1 :
First of all, we need to create a table and insert few records in it. This table will be used for reference in the view in later step.
--This script is compatible with SQL Server 2005 and above. USE tempdb GO --DROP TABLE [dbo].[tbl_Student] --GO CREATE TABLE [dbo].[tbl_Student]( [Sno] [int] NOT NULL, [Student ID] nvarchar(6) NOT NULL , [Student name] [varchar](50) NOT NULL ) Insert into dbo.[tbl_Student] VALUES (1,'STD001','Bob') Insert into dbo.[tbl_Student] VALUES (2,'STD002','Alexander') GO SELECT * FROM [tbl_Student] GO --OUTPUT
Step 2 :
Once you created a table, lets create a view on top of it. Given below is the script.
Please make a note that it is not recommended to create view using “*“. It must be with the proper column name of the table.
--This script is compatible with SQL Server 2005 and above. USE tempdb GO --DROP VIEW vw_Student --GO CREATE VIEW vw_Student AS SELECT * FROM [tbl_Student] GO SELECT * FROM vw_Student GO --OUTPUT
Step 3 :
Once you created a table & view and noticed that schema and table data is same whether in table or view, lets now alter the table and add one column namely “Address” and observe the impact on view . Given below is the script.
--This script is compatible with SQL Server 2005 and above. USE tempdb GO ALTER TABLE [tbl_Student] ADD [Address] varchar(100) GO SELECT * FROM [tbl_Student] GO SELECT * FROM vw_Student GO --OUTPUT
Step 4 :
Oopss…… If you look at the above result set you can observe that whatever changes you made in the table, did not reflect in the view. So what to do ? Here it comes to sp_refreshview (A system level stored procedure), which refreshes the metadata of any view once you edit the schema of the table. Lets execute the sp_refreshview
--This script is compatible with SQL Server 2005 and above. USE tempdb GO EXEC sp_refreshview N'dbo.vw_Student' GO SELECT * FROM [tbl_Student] GO SELECT * FROM vw_Student GO --OUTPUT
Conclusion :
The bottom line of this article is, whenever you alter any table and if there are views on top of it, do not forget to refresh the metadata of the views using sp_refreshview.
This sp_refreshview will work for only those views which have select * from table.Not for views where we select few fields of table.Right?
Hi Almas,
Thanks for your update. Somehow this is correct but not exactly, because sometime you alter the datatype in table, so even if you use the proper column name in the view, you need to refresh the metadata of views. This is one example but there are a lot of other cases where you must refresh to avoid any discrepancy in the view. In short, whenever there is a change in table, make sure you refresh the dependent views.
Imran
[…] in view of the bulk customization in the database structure. But I needed to customize the standard script to refresh multiple views simultaneously due to the scenarios given […]
[…] multiple tables and you need to refresh all views related to these tables. The standard script of standard script cannot accommodate this scenario, so I designed another script that fits this scenario. You just […]