代码之家 › 专栏 › 技术社区 › Jim McKeeth

如何在SQL Server 2008中重建视图

view sql-server-2008 tsql sql-server sql

Jim McKeeth · 技术社区 · 15 年前

我的数据库中有一个视图,有人用一个表中的*定义了它。我刚向该表添加了一个新列,希望视图反映新列。除了重新执行视图创建脚本之外,还有其他方法重建视图吗?我在找类似于 SPX编译 将重新编译存储过程(或更准确地标记它以便下次调用它时编译)。

更新: 在很长一段时间内,我尝试在视图上调用sp_recompile,但调用工作时,它没有重建视图。

更新2: 我希望能够从脚本中执行此操作。因此,将列添加到表中的脚本也可以更新视图。就像我说的,类似于sp_重新编译。

8 回复 | 直到 8 年前

Cory 15 年前

sp_refreshview [ @viewname = ] 'viewname'

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

Uwe Keim 11 年前

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 blog posting sp_refreshview stored procedure

gbn 15 年前

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

BogdanRB 11 年前

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;

Christoph 9 年前

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;

hamish 9 年前

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

thursdaysgeek 15 年前

MaxA 8 年前

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