SQL Server 2016 - Disparity Between View Definition and View Design

Question

I've run into an interesting issue with some databases I've inherited, and could use some help unpacking it all.

The Issue

After adding a column to a table, views selecting * from that table are now breaking in strange ways. The best way for me to illustrate is to repro it, so here goes.

Repro

Here's my table definitions:

CREATE TABLE TestTable1 (
    col1 INT,
    col2 varchar(12)
)

CREATE TABLE TestTable2 (
    col3 INT,
    col4 varchar(12)
)

And some test data:

INSERT INTO TestTable1 VALUES
  (1,'abc')
, (2,'def')
, (3,'ghi')
, (4,'jkl')

INSERT INTO TestTable2 VALUES
  (1,'ABC')
, (2,'DEF')
, (3,'GHI')
, (4,'JKL')

Now the View definition (and yes I know views should have an explicit column set. Whoever wrote this one did not):

CREATE VIEW TestView AS

SELECT 
    tt1.*
    , tt2.col4
FROM TestTable1 AS tt1
INNER JOIN TestTable2 AS tt2
ON tt1.col1 = tt2.col3
GO

As you'd expect, this is the result when I SELECT * FROM TestView:

col1        col2         col4
----------- ------------ ------------
1           abc          ABC
2           def          DEF
3           ghi          GHI
4           jkl          JKL

Super sensible. Here's where it runs off the rails - I add a column to TestTable1:

ALTER TABLE TestTable1
ADD col5 varchar(8)

And having done so, here's what the view now returns:

col1        col2         col4
----------- ------------ --------
1           abc          NULL
2           def          NULL
3           ghi          NULL
4           jkl          NULL

Because of course it does. However, if I run the same query used to define the view, I get:

col1        col2         col5     col4
----------- ------------ -------- ------------
1           abc          NULL     ABC
2           def          NULL     DEF
3           ghi          NULL     GHI
4           jkl          NULL     JKL

Which is exactly what I'd expect. So what's happened? I've tried every method I can find to query the view definition to see what's changed:

SELECT VIEW_DEFINITION FROM information_schema.views WHERE TABLE_NAME = 'TestView'

EXEC sp_helptext 'TestView'

SELECT definition 
FROM sys.objects AS o
JOIN sys.sql_modules m ON m.object_id = o.object_id
WHERE o.object_id = object_id( 'dbo.TestView')
  and o.type      = 'V'

And each one returns the definition exactly as it was originally defined. It's not until I use the SQL Server Management Studio Design tool that I see something different:

SELECT        tt1.col1, tt1.col2, tt1.col5 AS col4, tt2.col4
FROM            dbo.TestTable1 AS tt1 INNER JOIN
                         dbo.TestTable2 AS tt2 ON tt1.col1 = tt2.col3

It's the work of a lunatic. It's easily fixed by updating the view to use explicit columns rather than the wildcard *. However, what I'm seeing is that this database and table are referenced in myriad views across multiple databases.

The Question

So my question is thus - where is the metadata/definition for this nonsensical version of the view, and why does it trump what is supposedly the unambiguous definition of the view?

Answer 1

The view will run according to it's metadata, which is no longer correct.

If you run sp_refreshview, it should work correctly.

sp_refreshview  @viewname =  'TestView' ;

select * from TestView ;

+------+------+------+------+
| col1 | col2 | col5 | col4 |
+------+------+------+------+
|    1 | abc  | NULL | ABC  |
|    2 | def  | NULL | DEF  |
|    3 | ghi  | NULL | GHI  |
|    4 | jkl  | NULL | JKL  |
+------+------+------+------+