Sql view update issue

Question

I am having a problem with sql view. My actual views cover several associations, but for the purposes of my question I will demonstrate the problem with smaller examples.

Say I have views ...

create view A as select Id as IdC from tableA go create view B as select b.Id, b.Name, a.* from tableB b inner join A a on a.Id = b.Id go 

So, all is well. Then I change view A to read ...

 alter view A as select Id as IdColumn from tableA go 

So now, if I write ...

 select * from A 

It returns an IdColumn column

However, if I write ...

 select * from B 

It returns the original IdC column name from view A

I tried sp_refreshview , but that didn't help.

How can I get view B to return the updated column name from view A?

UPDATE **

Ok, I messed up the original question. I thank everyone for their answers. I intend to join view A to table B in view B. It seems the alter operator in view B solves the problem.

Answer 1

Problem with select *

If you specified View A

 create view B as select b.Id, b.Name, a.* from tableB b inner join A on a.Id = b.Id 

Changing the view The returned columns (add, delete, rename) will not be displayed in view B until you see Alter . The same thing happens in UDF and stored procedures.

Answer 2

As I can see, you are asking if you are referencing tableA not view A

 select b.Id, b.Name, a.* from tableB b inner join tableA a on a.Id = b.Id 

Thus, changing the above query will allow you to solve the problem

Modified query to view B

  select b.Id, b.Name, a.* from tableB b inner join A a on a.IdColumn = b.Id 

Answer 3

Your viewB is connecting to table A, but not seeing A, try:

 inner join A a on a.Id = b.Id 

Answer 4

View B has nothing to do with View A.

In view B, you have TableA and TableB

Try instead:

 alter view B as select b.Id, b.Name, a.* from tableB b inner join A a on a.IdColumn = b.Id go