The old schema refresh problem with SQL Server 7.0 still exists in SQL 2005

The metadata in SQL Server 2000 and 2005 databases do not automatically update for views when you add to or remove a column to the underlying table. They will only show the original columns from when the view is updated. For example, if you run the following script, you will only be shown the first 2 columns even though there are now 3 columns in the table:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Client]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Client]
GO


CREATE TABLE [dbo].[Client] (
    [ClientID] [int] IDENTITY (1, 1) NOT NULL ,
    [Name] [varchar] (50) NULL ,
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vwClient]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[vwClient]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.vwClient
AS
SELECT     dbo.Client.*
FROM         dbo.Client


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Client ADD
    DateUpdated datetime NULL
GO
COMMIT


SELECT * FROM vwClient

Even worse, if you remove a column, you will get runtime errors guaranteed if you don't recreate all dependant views. To fix this, you should perform an sp_refreshview 'viewname' on the views whose underlying tables have been updated - or you can manually trigger this by going to design view of the view, adding a space to the query definition and clicking OK.

The simplest way to do this is for all views is to use the following stored procedure to detect any changes or problems:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procRefreshAllViews]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procRefreshAllViews]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO



CREATE PROC dbo.procRefreshAllViews
AS

SET quoted_identifier off

DECLARE @ObjectName varchar (255)
DECLARE @ObjectName_header varchar (255)

DECLARE tnames_cursor CURSOR FOR SELECT name FROM sysobjects
    WHERE type = 'V' AND uid = 1 Order By Name
OPEN tnames_cursor
FETCH NEXT FROM tnames_cursor INTO @Objectname
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
        SELECT @ObjectName_header = 'Refreshing ' + @ObjectName
        PRINT @ObjectName_header
        EXEC('sp_refreshview ' + @ObjectName)
    END
    FETCH NEXT FROM tnames_cursor INTO @ObjectName
END
PRINT ' '
SELECT @ObjectName_header = 'ALL VIEWS HAVE BEEN REFRESHED'

PRINT @ObjectName_header
DEALLOCATE tnames_cursor
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

This problem can be detected easily with SSW SQL Auditor which you can get from http://www.ssw.com.au/ssw/sqlauditor/