Cómo reconstruir la vista en SQL Server 2008

Para reconstruir todas las vistas de una base de datos SQL Server, puede usar el siguiente 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;

Esta es una versión ligeramente modificada de esta publicación del blog. Utiliza el sp_refreshview el procedimiento almacenado también.

Además de la respuesta de Cory, puede definirla correctamente utilizando schemabinding y la lista completa de columnas.

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

Script ligeramente modificado que actualiza todas las vistas, llama a sp_recompile, sp_refresh y obtiene la lista de sys.vistas:

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;

Sp_refreshview no parece ser relyable! Cuando utilicé el código de Uwe Keim / BogdanRB obtuve muchos errores, incluso si la vista no tiene referencias inválidas. El siguiente código hizo el truco para mí (para determinar qué vista no es válida después de los cambios de esquema):

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;

Aquí está mi script favorito para esto (Modificé un antiguo script de comprobación sp_exec que tenía), usa EXEC sp_refreshsqlmodule @name

SET NOCOUNT ON;

-- Set ViewOnly to 1 to view missing EXECUTES. Set to 0 to correct missing EXECUTEs
DECLARE
      @ViewOnly INT; SET @ViewOnly = 0;

-- Role to set execute permission on.
DECLARE 
      @ROLE  sysname ; set @ROLE = QUOTENAME('spexec');

DECLARE 
      @ID      INT,
    @LAST_ID INT,
      @NAME NVARCHAR(2000),
      @SQL  NVARCHAR(2000);

DECLARE @Permission TABLE (
      id INT IDENTITY(1,1) NOT NULL,
      spName  NVARCHAR(2000),
      object_type NVARCHAR(2000),
      roleName  NVARCHAR(2000),
      permission  NVARCHAR(2000),
      state  NVARCHAR(2000)
)

--Initialise the loop variable
SET @LAST_ID = 0
--Get all the stored procs into a temp table. 
WHILE @LAST_ID IS NOT NULL
BEGIN
    -- Get next lowest value
    SELECT @ID = MIN(object_id)
    FROM sys.objects 
    WHERE object_id > @LAST_ID  
      -- Looking for Stored Procs, Scalar, Table and Inline Functions
            AND type IN ('P','FN','IF','TF','AF','FS','FT','PC', 'V')

    SET @LAST_ID = @ID

    IF @ID IS NOT NULL
    BEGIN
            INSERT INTO @Permission
            SELECT o.name,
                  o.type_desc, 
                  r.name,  
                  p.permission_name,  
                  p.state_desc 
            FROM sys.objects AS o
            LEFT outer JOIN sys.database_permissions AS p
                  ON p.major_id = o.object_id
            LEFT OUTER join sys.database_principals r 
                  ON p.grantee_principal_id = r.principal_id
            WHERE o.object_id = @ID 
                  AND o.type IN ('P','FN','IF','TF','AF','FS','FT','PC', 'V')  
                  --Exclude special stored procs, which start with dt_...
                  AND NOT o.name LIKE 'dt_%'
                  AND NOT o.name LIKE 'sp_%'
                  AND NOT o.name LIKE 'fn_%'
      END   
END

--GRANT the Permissions, only if the viewonly is off.
IF ISNULL(@ViewOnly,0) = 0 
BEGIN
      --Initialise the loop variable
      SET @LAST_ID = 0
      WHILE @LAST_ID IS NOT NULL
      BEGIN
            -- Get next lowest value
            SELECT @ID = MIN(id)
            FROM @Permission 
            WHERE roleName IS NULL
                  AND id > @LAST_ID

            SET @LAST_ID = @ID

            IF @ID IS NOT NULL
            BEGIN
                  SELECT @NAME = spName
                  FROM @Permission 
                  WHERE id = @ID

                  PRINT 'EXEC sp_refreshsqlmodule ' + @NAME
                  -- Build the DCL to do the GRANT
                  SET @SQL = 'sp_refreshsqlmodule [' + @NAME + ']'

                  -- Run the SQL Statement you just generated
                  EXEC (@SQL)
            END
      END

      --Reselect the now changed permissions
      SELECT o.name,
            o.type_desc, 
            r.name,  
            p.permission_name,  
            p.state_desc 
      FROM sys.objects AS o
      LEFT outer JOIN sys.database_permissions AS p
            ON p.major_id = o.object_id
      LEFT OUTER join sys.database_principals r 
            ON p.grantee_principal_id = r.principal_id
      WHERE o.type IN ('P','FN','IF','TF','AF','FS','FT','PC', 'V') 
            AND NOT o.name LIKE 'dt_%'
            AND NOT o.name LIKE 'sp_%'
            AND NOT o.name LIKE 'fn_%'
      ORDER BY o.name
END
ELSE
BEGIN
      --ViewOnly: select the stored procs which need EXECUTE permission.
      SELECT *
      FROM @Permission 
      WHERE roleName IS NULL
END

Haga clic con el botón derecho en la vista y elija Actualizar en el menú emergente?

Puedes usar este sp:

CREATE PROCEDURE dbo.RefreshViews 
    @dbName nvarchar(100) = null 
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @p nvarchar(250) = '@sql nvarchar(max) out'
    DECLARE @q nvarchar(1000)
    DECLARE @sql nvarchar(max)

    if @dbName is null
        select @dbName = DB_NAME()

    SELECT @q = 'SELECT @sql = COALESCE(@sql + '' '', '''') + ''EXEC sp_refreshview ''''[' + @dbName + '].['' + TABLE_SCHEMA + ''].['' + TABLE_NAME + '']'''';'' 
                FROM [' + @dbName + '].INFORMATION_SCHEMA.Views  '

    EXEC sp_executesql @q , @p ,@sql out

    EXEC sp_executesql @sql     


END
GO