I've run into an interesting issue with some databases I've inherited, and could use some help unpacking it all.
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.
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.
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?