December 3, 2007 at 4:01 pm
Comments posted to this topic are about the item Refresh All Views
January 28, 2008 at 7:32 am
Numerous issues with this script. A lot of extra logic. It can be streamlined.
1. Why all the dynamic SQL?
2. The names of the views should be obtained from information_schema.tables WHERE table_type = 'VIEW'.
3. When refreshing a view, you should also include the owner (schema). If there is more than one schema in the database, an error will result when you're refreshing the view and are executing as another owner (e.g., "dbo").
4. There is no need for dynamic SQL when executing sp_refreshview.
5. If dynamic SQL is to be used, procedure sp_executesql should be used vs. EXECUTE.
6. I don't see the need for a temp table. a LOCAL FAST_FORWARD cursor against information_schema will work.
Simpler logic attached.
June 26, 2008 at 11:24 pm
Hi BeoWulf and John,
Both the scripts provided are good and working as well.
But whenever any of the view is not getting refreshed due to Binding errors it is exiting out from the cursor and it is not refreshing views after that failed view.
Can anyone suggest me how to fix this.Means I wanted to exclude (or print the view name which is not refreshed) the un refreshed view and
refresh the other views.
June 27, 2008 at 7:18 am
Can anyone suggest me how to fix this.Means I wanted to exclude (or print the view name which is not refreshed) the un refreshed view and refresh the other views.
The script is coded to exit on the first error. So add a TRY ... CATCH around the refreshing statement. Print out the name in the CATCH block.
If you don't know about the TRY ... CATCH syntax introduced in SQL Server 2005, this is a good script to work your first implementation. See the BOL for syntax as well as a search on this forum. There are numerous threads.
November 9, 2009 at 3:59 am
You can simply use this stored procedure to refresh all database views with one command (including dependent views problem handling - Binding errors)
Note : this procedure is examined on SQL Server 2005 .
CREATE procedure [dbo].[refviews] (@varViewNameVARCHAR(500)= NULL )
--Created on 08 Feb 2009 By Ahmed Omara
as
declare @p_viewname nvarchar(500)
BEGIN
IF (@varViewName IS NOT NULL OR @varViewName <>'')
--Validate certain view specified.
BEGIN
SET @varViewName = ('['+@varViewName+']');
PRINT 'Refreshing View...' + @varViewName;
EXEC sp_refreshview @varViewName;
END;
ELSE
BEGIN
--CREATE CURSOR TO READ THE DATABASE VIEWS IN ORDER TO VALIDATE
Declare @CrsrView Cursor
Set @CrsrView = Cursor For
SELECT [name]
FROM sysObjects
WHERExType = 'V'
AND [name] NOT IN ('syssegments', 'sysconstraints')
AND category=0
ORDER BY crdate
-- fetch the first record in the Cursor
Open @CrsrView
Fetch Next From @CrsrView Into @p_viewname
While (@@FETCH_STATUS = 0) --WHILE THE CURSER STILL INCLUDE RECORDS
BEGIN
--refresh the view
SET @varViewName = ('['+@varViewName+']');
PRINT 'Refreshing View...' + @p_viewname;
EXEC sp_refreshview @p_viewname;
-- fetch the next record in the Cursor
Fetch Next From @CrsrView Into @p_viewname
END
Close @CrsrView
Deallocate @CrsrView
PRINT 'Refresh process is done successfully'
END;
End;
/*
To Run the procedure for all views type:
EXEC refviews
To Run the procedure for certain view (Ex. view "VWMMS_MR") type :
EXEC refviews VWMMS_MR
*/
March 13, 2012 at 4:31 am
Just in case anyone was looking for a post SS 2k version that avoids cursors (and dare I say more readable?) version.
/*
uspRefreshViews
Date: 2012-03-13
purpose: to refresh the schema bindings of the current db
usage: exec dbo.uspRefreshViews
comments:
omitted error handling (everyone has their own-rolled way which is best) and single view refresh
*/
create proc dbo.uspRefreshViews
as
begin
set nocount on
declare @views table
(id int identity(1,1) not null,
schemaName NVARCHAR(128),
viewName nvarchar(128) not null
);
declare
@maxID int,
@id int = 0,
@aViewName nvarchar(128) = '',
@aSchemaViewName NVARCHAR(258)
insert into @views
select sch.name, vw.name
FROM sys.viewsvw
inner join sys.schemas sch on sch.schema_id = vw.schema_id
select @maxID = coalesce(@@ROWCOUNT,0)
while @id <> @maxID
begin
select top 1 @id = id, @aSchemaViewName = schemaName + '.' + viewName
from @views
where @id < id
order by id asc
print 'Refreshing '+@aSchemaViewName
EXECUTE sp_refreshview @aSchemaViewName
end
end
Kind regards
Derek
June 20, 2012 at 12:53 pm
While this works great when views are valid it crashes the proc if a view won't refresh correctly.
It seems to be fine when running this with the sp_refreshsqlmodule on functions and stored procedures...if validation fails it reports it and lets you move on to the next one.
On views it always errors out...it shows the validation error and then crashes on the next view with the following error:
refreshing...[dbo].[vwA]
Validation failed for : [dbo].[vwA], Error:Invalid column name 'address'.
refreshing...[dbo].[vwB]
Validation failed for : [dbo].[vwB], Error:The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Msg 3998, Level 16, State 1, Line 1
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.
Any ideas on how to trap this and move on to the next...I'm trying to generate a list of faulty views.
thanks
PS Try Catch does NOT work on views....works like a charm on Stored Procs
here is my try catch logic:
WHILE @Cnt <= @Tot BEGIN
SELECT @spname = spname
FROM @v-2
WHERE RecID = @Cnt
PRINT 'refreshing...' + @spname
BEGIN TRY
EXEC sp_refreshsqlmodule @spname
END TRY
BEGIN CATCH
PRINT 'Validation failed for : ' +
@spname + ', Error:' +
ERROR_MESSAGE()
END CATCH
SET @Cnt = @cnt + 1
END
June 20, 2012 at 2:53 pm
Have you tried calling the proc recursively in the catch block with a parameter indicating the progress?
June 20, 2012 at 4:09 pm
I figured it out...works like a charm
SET XACT_ABORT ON;
BEGIN TRY
-- refresh the stored procedure
BEGIN TRANSACTION;
-- refresh the proc/view/function
EXEC sp_refreshsqlmodule @spname
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF (XACT_STATE()) = -1
BEGIN
PRINT 'Validation failed for : Type ' +
@type + ', Name: ' + @spname + ', Error:' + ERROR_MESSAGE()
ROLLBACK TRANSACTION;
END;
END CATCH
May 4, 2013 at 7:05 am
You are Fantastic.... This is the best solution I ever got... 🙂
Thank you 🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply