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).
在我的数据库中有一个视图,有人用一个表定义了*。我刚刚在该表中添加了一个新列,我希望该视图能够反映新列。除了重新执行视图创建脚本之外,还有另一种重建视图的方法吗?我正在寻找类似于sp_recompile将如何重新编译存储过程(或更准确地标记它下次被调用时编译)的东西。
Update: On a long shot I tried calling sp_recompile on the view and while the call worked, it didn't rebuild the view.
更新:在远景中,我尝试在视图上调用sp_recompile,并且在调用工作时,它没有重建视图。
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.
更新2:我希望能够从脚本中执行此操作。因此,将列添加到表中的脚本也可以更新视图。就像我说的那样,类似于sp_recompile。
8 个解决方案
#1
40
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.
更新指定的非架构绑定视图的元数据。由于视图所依赖的基础对象的更改,视图的持久元数据可能会过时。
http://technet.microsoft.com/en-us/library/ms187821.aspx
http://technet.microsoft.com/en-us/library/ms187821.aspx
#2
12
In order to rebuild all views of a SQL Server database, you could use the following script:
要重建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;
This is a slightly modified version from this blog posting. It uses the sp_refreshview
stored procedure, too.
这是此博客文章的略微修改版本。它也使用sp_refreshview存储过程。
#3
5
As well as Cory's answer, you could define it properly using schemabinding and the full column list.
除了Cory的答案,您可以使用schemabinding和完整列列表正确定义它。
CREATE VIEW MyView
WITH SCHEMABINDING
AS
SELECT
col1, col2, col3, ..., coln
FROM
MyTable
GO
#4
2
Slightly modified script that refreshes all views, calls sp_recompile, sp_refresh and gets list from sys.views:
稍微修改过的脚本,刷新所有视图,调用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
2
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):
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
1
Here is my favorite script for this (I modified an old sp_exec checking script I had), it uses EXEC sp_refreshsqlmodule @name
这是我最喜欢的脚本(我修改了一个旧的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
#7
0
Right-click on the view and choose Refresh from the popup menu?
右键单击视图,然后从弹出菜单中选择“刷新”?
#8
0
You can use this sp:
你可以使用这个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
#1
40
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.
更新指定的非架构绑定视图的元数据。由于视图所依赖的基础对象的更改,视图的持久元数据可能会过时。
http://technet.microsoft.com/en-us/library/ms187821.aspx
http://technet.microsoft.com/en-us/library/ms187821.aspx
#2
12
In order to rebuild all views of a SQL Server database, you could use the following script:
要重建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;
This is a slightly modified version from this blog posting. It uses the sp_refreshview
stored procedure, too.
这是此博客文章的略微修改版本。它也使用sp_refreshview存储过程。
#3
5
As well as Cory's answer, you could define it properly using schemabinding and the full column list.
除了Cory的答案,您可以使用schemabinding和完整列列表正确定义它。
CREATE VIEW MyView
WITH SCHEMABINDING
AS
SELECT
col1, col2, col3, ..., coln
FROM
MyTable
GO
#4
2
Slightly modified script that refreshes all views, calls sp_recompile, sp_refresh and gets list from sys.views:
稍微修改过的脚本,刷新所有视图,调用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
2
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):
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
1
Here is my favorite script for this (I modified an old sp_exec checking script I had), it uses EXEC sp_refreshsqlmodule @name
这是我最喜欢的脚本(我修改了一个旧的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
#7
0
Right-click on the view and choose Refresh from the popup menu?
右键单击视图,然后从弹出菜单中选择“刷新”?
#8
0
You can use this sp:
你可以使用这个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