Как перестроить представление в SQL Server 2008

В моей БД есть представление, что кто-то определил с помощью * из одной таблицы. Я просто добавил новый столбец в эту таблицу и хочу, чтобы представление отображало новый столбец. Помимо повторного выполнения сценария создания представления, есть ли другой способ перестроить представление? Я ищу что-то похожее на то, как sp_recompile перекомпилирует хранимую процедуру (или точнее пометит ее для компиляции при следующем вызове).

Обновление: в конце концов я попытался вызвать sp_recompile для представления, и пока вызов работал, он не перестраивал представление.

Обновление 2: я хотел бы быть в состоянии сделать это из сценария. Таким образом, скрипт, который добавляет столбцы в таблицу, также может обновить представление. Как я уже сказал, что-то похожее на sp_recompile.

12.10.2009 23:34:02
Так каково ваше намерение? Вы пытаетесь минимизировать задержку при первом доступе?
dance2die 12.10.2009 23:48:15
Представление не отражает новые столбцы в таблице. Я хочу заставить представление включить новые столбцы.
Jim McKeeth 13.10.2009 07:02:34
8 ОТВЕТОВ
РЕШЕНИЕ

Я верю, что вы ищете

sp_refreshview [ @viewname = ] 'viewname'

Обновляет метаданные для указанного представления без привязки к схеме. Постоянные метаданные для представления могут устареть из-за изменений базовых объектов, от которых зависит представление.

http://technet.microsoft.com/en-us/library/ms187821.aspx

41
13.10.2009 02:06:54
Это хорошо, но у меня есть один вопрос, если в моем представлении больше таблицы, и я добавляю столбец, который вызовет конфликт при добавлении в представление, то что произойдет?
KuldipMCA 25.12.2011 08:23:15

Щелкните правой кнопкой мыши на представлении и выберите «Обновить» во всплывающем меню?

0
12.10.2009 23:37:40
Я хотел бы быть в состоянии сделать это из сценария. Таким образом, скрипт, который добавляет столбцы в таблицу, также может обновить представление. Как я уже сказал, что-то похожее на sp_recompile.
Jim McKeeth 12.10.2009 23:40:59

Как и ответ Кори, вы можете правильно определить его, используя привязку схемы и полный список столбцов.

CREATE VIEW MyView
WITH SCHEMABINDING
AS
SELECT
    col1, col2, col3, ..., coln
FROM
    MyTable
GO
5
13.10.2009 04:34:27

Чтобы перестроить все представления базы данных SQL Server, вы можете использовать следующий скрипт:

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;

Это слегка измененная версия из этого блога . Он также использует sp_refreshviewхранимую процедуру .

13
23.03.2013 17:18:51
В большинстве моих представлений возникает ошибка с этим сценарием (Ошибка при обновлении представления «xxx».) Даже если представление является правильным, и я могу выполнить «SELECT * FROM xxx». Может ли быть так, что эта хранимая процедура не может обрабатывать представления, которые включают другие представления в качестве исходных «таблиц»?
Christoph 19.11.2014 08:08:45

Слегка измененный скрипт, который обновляет все представления, вызывает sp_recompile, sp_refresh и получает список из 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;
2
6.01.2014 22:04:44

sp_refreshview не выглядит надежным! Когда я использовал код из Uwe Keim / BogdanRB, я получил много ошибок, даже если представление не имеет недопустимых ссылок! Следующий код помог мне (чтобы определить, какое представление недопустимо после изменения схемы):

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;
2
7.07.2015 11:31:49

Вот мой любимый скрипт для этого (я изменил старый скрипт проверки sp_exec, который у меня был), он использует 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
1
19.11.2015 00:20:02

Вы можете использовать этот 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
0
15.06.2016 10:34:29