How to rebuild view in SQL Server 2008

Question

There is a view in my DB that someone defined with a * from one table. I just added a new column to that table and I want the view to reflect the new column. Besides re-executing the view creation script, is there another way to rebuild the view? I am looking for something similar to how sp_recompile will recompile a stored procedure (or more accurately flag it to be compiled next time it is called).

Update: On a long shot I tried calling sp_recompile on the view and while the call worked, it didn't rebuild the view.

Update 2: I would like to be able to do this from a script. So the script that adds the columns to the table could also update the view. So like I said, something similar to sp_recompile.

Solution 1

I believe what you're looking for is

sp_refreshview [ @viewname = ] 'viewname'

Updates the metadata for the specified non-schema-bound view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends.

See Microsoft Docs

Solution 2

In order to rebuild all views of a SQL Server database, you could use the following script:

DECLARE @view_name AS NVARCHAR(500);

DECLARE views_cursor CURSOR FOR 
    SELECT TABLE_SCHEMA + '.' +TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_TYPE = 'VIEW' 
    AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsMsShipped') = 0 
    ORDER BY TABLE_SCHEMA,TABLE_NAME 

OPEN views_cursor 

FETCH NEXT FROM views_cursor 
INTO @view_name 

WHILE (@@FETCH_STATUS <> -1) 
BEGIN
    BEGIN TRY
        EXEC sp_refreshview @view_name;
        PRINT @view_name;
    END TRY
    BEGIN CATCH
        PRINT 'Error during refreshing view "' + @view_name + '".';
    END CATCH;

    FETCH NEXT FROM views_cursor 
    INTO @view_name 
END 

CLOSE views_cursor; 
DEALLOCATE views_cursor;

This is a slightly modified version from this blog posting. It uses the sp_refreshview stored procedure, too.

Solution 3

As well as Cory's answer, you could define it properly using schemabinding and the full column list.

CREATE VIEW MyView
WITH SCHEMABINDING
AS
SELECT
    col1, col2, col3, ..., coln
FROM
    MyTable
GO

Solution 4

Slightly modified script that refreshes all views, calls sp_recompile, sp_refresh and gets list from sys.views:

DECLARE @view_name AS NVARCHAR(500);
DECLARE views_cursor CURSOR FOR SELECT DISTINCT name from sys.views
OPEN views_cursor 

FETCH NEXT FROM views_cursor 
INTO @view_name 

WHILE (@@FETCH_STATUS <> -1) 
BEGIN
    BEGIN TRY
        EXEC sp_recompile @view_name;
        EXEC sp_refreshview @view_name;
        PRINT @view_name;
    END TRY
    BEGIN CATCH
        PRINT 'Error during refreshing view "' + @view_name + '".';
    END CATCH;

    FETCH NEXT FROM views_cursor 
    INTO @view_name 
END 

CLOSE views_cursor; 
DEALLOCATE views_cursor;

Solution 5

sp_refreshview does not seem to be relyable! When I used the code from Uwe Keim/BogdanRB I got many errors even if the view has no invalid references! The following code did the trick for me (to determine which view is invalid after schema changes):

DECLARE @view_name AS NVARCHAR(500);
DECLARE @Query AS NVARCHAR(600);
SET @Query = '';
DECLARE views_cursor CURSOR FOR SELECT DISTINCT ('[' + SCHEMA_NAME(schema_id) + '].[' + name + ']') AS Name FROM sys.views
OPEN views_cursor 

FETCH NEXT FROM views_cursor 
INTO @view_name 

WHILE (@@FETCH_STATUS <> -1) 
BEGIN
        EXEC sp_recompile @view_name;
        SELECT @Query = 'SELECT ''' + @view_name + ''' AS Name, COUNT(*) FROM ' + @view_name + ' AS Count; ';
        EXEC (@Query);
        -- PRINT @view_name;

    FETCH NEXT FROM views_cursor 
    INTO @view_name 
END 

CLOSE views_cursor; 
DEALLOCATE views_cursor;

View more solutions