I'm selecting from a view in SQL Server. If I right-click on it and choose "Select Top n Rows", it runs and shows me 30 columns. If I open the view in ALTER mode (right-click, Script View As, etc…), highlight the code and run it, I get 50 columns. I should get 50 columns, because I just added 20 new columns to the table feeding the view, and inside the view I'm using a "SELECT * FROM…" statement.
I've refreshed the Views folder, but that didn't help.
Anyone know why the discrepancy, and how to correct it?
Best Answer
First, stop using
SELECT *
in your views. I talk about this quite a bit here:Next, run
sp_refreshview
orsp_refreshsqlmodule
against each view that references a table (or another view!) that you have changed, e.g.:If you want to generate a script that refreshes all of the views in the system:
(You may want to run it twice in case there are circular or out-of-order dependencies.)
But this is a temporary fix. You will have to repeat this every time you change any table that might have views that reference it, then go find out which views are involved. This is not the right way to approach schema design. AT ALL. I wrote about a DDL trigger that would help automate this for you, but this is really the wrong approach - just stop using
SELECT *
in your views!You may also want to consider adding
WITH SCHEMABINDING
to the definition of all of your views. This does two things:SELECT *
in the view. This is A GOOD THING™.