View in one schema and table in another schema

Question

I have a Table created by db_owner

CREATE TABLE [dbo].[TableTest](
[ID] [int] NULL,
[Cost] [int] NULL,
[partnumber] [int] NULL
) ON [PRIMARY]

Another table with the same definition, but in an another custom schema.

CREATE TABLE [Test].[TableTest](
[source] [int] NULL,
[cost] [int] NULL,
[partnumber] [int] NULL
) ON [PRIMARY]

If I create a view like so:

create view [Test].[ViewTest] as select * from dbo.TableTest

When I do a select on the newly created view I obtain the results from the Test.TableTest, not from dbo.TableTest as expected. What is happening?

Answer 1

This may happens if after you created the Test.MyView you change the structure of the base tables (example: you add / remove columns / drop & recreate with other columns [dbo].[TableTest]). You have to use sp_refreshview.

CREATE TABLE [dbo].[TableTest](
[ID] [int] NULL,
[Cost] [int] NULL,
[partnumber] [int] NULL
) ON [PRIMARY]
GO

CREATE SCHEMA Test
GO

CREATE TABLE [Test].[TableTest](
[source] [int] NULL,
[cost] [int] NULL,
[partnumber] [int] NULL
) ON [PRIMARY]
GO

CREATE VIEW [Test].[ViewTest] as select * from dbo.TableTest
GO

SELECT * FROM [Test].[ViewTest] 
/*
ID          Cost        partnumber
----------- ----------- -----------

(0 row(s) affected)
*/
GO

DROP TABLE [dbo].[TableTest];
GO
CREATE TABLE [dbo].[TableTest](
[source] [int] NULL,
[cost] [int] NULL,
[partnumber] [int] NULL
) ON [PRIMARY]
GO

-- From this moment Test.ViewTest should include source, cost and partnumber columns -- but if we execute SELECT * FROM [Test].[ViewTest] SQL Server will show the same "old" columns

SELECT * FROM [Test].[ViewTest] 
/*
ID          Cost        partnumber
----------- ----------- -----------

(0 row(s) affected)
*/
GO

-- The solution: sp_refreshview

EXEC sp_refreshview 'Test.ViewTest'
GO
SELECT * FROM [Test].[ViewTest] 
/*
source      cost        partnumber
----------- ----------- -----------

(0 row(s) affected)
*/
GO