Подтвердить что ты не робот

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

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

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

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

4b9b3361

ОТВЕТЫ

Ответ 1

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

sp_refreshview [ @viewname = ] 'viewname'

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

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

Ответ 2

Чтобы восстановить все представления базы данных SQL Server, вы можете использовать следующие 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;

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

Ответ 3

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

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

Ответ 4

Немного измененный script, который обновляет все представления, вызывает 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;

Ответ 5

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;

Ответ 6

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

Ответ 7

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

Ответ 8

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