When views lie

Views don’t lie you say.

– They only show the data in the underlying tables.

Not always. Not if the meta data in the underlying tables has changed.

This is because views are not re-compiled when meta data is changed.

However, you can programatically re-compile all views in a database using ‘sp_refreshview’ to make sure they are all up to date.

All views in a database can be found in the table ‘sys.views’.

Here is the code:

/*
* Drop table PersonAddress if it exists
*/
IF OBJECT_Id(‘dbo.PersonAddress’, ‘U’) IS NOT NULL
DROP TABLE dbo.PersonAddress

/*
* Drop table Address if it exists
*/
IF OBJECT_Id(‘dbo.Address’, ‘U’) IS NOT NULL
DROP TABLE dbo.Address

/*
* Drop table Person if it exists
*/
IF OBJECT_Id(‘dbo.Person’, ‘U’) IS NOT NULL
DROP TABLE dbo.Person

/*
* Create table Person
*/
CREATE TABLE [dbo].[Person](
[PersonId] [int] PRIMARY KEY CLUSTERED NOT NULL,
[FirstName] [varchar](20) NOT NULL,
[LastName] [varchar](20) NOT NULL
);

/*
Create table Address
*/
CREATE TABLE [dbo].[Address](
[AddressId] [int] PRIMARY KEY CLUSTERED NOT NULL,
[Location] [varchar](20) NOT NULL
);

/*
Create table PersonAddress
*/
CREATE TABLE [dbo].[PersonAddress](
[PersonId] [int] NOT NULL,
[AddressId] [int] NOT NULL ,
);

/*
* Insert data
*/
INSERT INTO Person(PersonId,FirstName,LastName)
VALUES(1,’Dave’,’Bowman’),
(2,’Frank’,’Poole’),
(3,’Heywood’,’Floyd’);

INSERT INTO Address(AddressId,Location)
VALUES(1,’Discovery One’),
(2,’Earth’);

INSERT INTO PersonAddress(PersonId,AddressId)
VALUES(1,1),(2,1),(3,2);

SELECT ‘All data’
SELECT * FROM Person
SELECT * FROM Address
SELECT * FROM PersonAddress
GO

/*
* Drop view vPersonAddress if it exists
*/
IF OBJECT_Id(‘dbo.vPersonAddress’, ‘V’) IS NOT NULL
DROP VIEW dbo.vPersonAddress
GO
CREATE VIEW dbo.vPersonAddress
AS
SELECT p.*,a.*
FROM PersonAddress pa
JOIN Person p on p.PersonId = pa.PersonId
JOIN Address a on a.AddressId = pa.AddressId
GO

SELECT ‘All data: vPersonAddress’
SELECT * FROM vPersonAddress
GO
/*
* Change table Person
*/
ALTER TABLE [dbo].[Person]
ADD [Initials] [varchar](20)
GO
/*
* Update table Person
*/
UPDATE dbo.Person
set Initials = ‘DB’
WHERE PersonId=1;
UPDATE dbo.Person
set Initials = ‘FP’
WHERE PersonId=2;
UPDATE dbo.Person
set Initials = ‘HF’
WHERE PersonId=3;

GO

SELECT ‘All data: vPersonAddress. After changing Person-table. The data is a mess.’
SELECT * FROM vPersonAddress
GO
/*
* Drop table #views if it exists
*/
IF OBJECT_ID(‘tempdb..[#views]’) IS NOT NULL
DROP TABLE #views;
/*
* Store all view names in table #views
*/
SELECT ss.name + ‘.’ + sv.name AS ViewName
INTO #views
FROM sys.views sv
JOIN sys.schemas ss
ON ss.schema_id = sv.schema_id
ORDER BY ss.name,sv.name
/*
* Run ‘sp_refreshview’ on all views in table #views
*/
DECLARE @ViewName VARCHAR(1000);
DECLARE @SQL VARCHAR(1000);
DECLARE ViewCursor CURSOR
FOR
SELECT ViewName
FROM #views;
OPEN ViewCursor;
FETCH NEXT FROM ViewCursor INTO @ViewName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = ‘EXEC sp_refreshview [‘ + @ViewName+’];’
–SELECT @SQL;
EXECUTE (@SQL);
FETCH NEXT FROM ViewCursor INTO @ViewName;
END;
CLOSE ViewCursor;
DEALLOCATE ViewCursor;

GO
SELECT ‘All data: vPersonAddress. After that EXEC sp_refreshview [dbo.vPersonAddress]; has been run’

SELECT * FROM vPersonAddress
GO