Views & Inline Functions
Compare and synchronize Views & Inline Functions.
Create
Create new ones, if they do not exist in the target database.
Recreate
Recreate (drop and create) existing ones to remove the differences.
Drop
Drop existing ones, if they do not exist in the source database.
Recreate All
Recreate all objects of a given type in the target database.
ANSI_NULLS
If this option is not set, the comparison algorithm will ignore the ANSI_NULLS property of the views when comparing, however, if the view is re-created due to other differences, the new value will be taken.
Append refresh of Views & Inline Functions
Append to the synchronization script the special algorithm to refresh Views & Inline Functions in the particular order.
Refresh by altered dependencies
Refresh all objects that are referencing to the altered or recreated objects which were modified since the preparatory script was executed.
Refresh by overdue dependencies
Refresh all objects that are referencing to the objects with a newer date of update.
All
Refresh all objects.
Update metadata sp_refreshview and sp_refreshsqlmodule
The final step of a database structure sinchronization is refreshing the views and inline functions metadata. Below is a part of the algorithm that refreshes views and inline functions in an iterative way in an required order based on the dependencies between objects and their last update date.
The type of algorithm is determined by the selected options in Append refresh of Views & Inline Functions.
...
WHILE @@FETCH_STATUS = 0 BEGIN
SET @found = @found + 1;
IF -- skip with schemabinding
@is_schema_bound = 0
-- skip if it is not referencing to anything
AND @last_referenced_modify_date IS NOT NULL AND (
-- the referenced object has been changed by this synchronization script
@last_referenced_modify_date > @sync_start_date
-- the referenced object is up to date
OR (@is_refresh_overdue = 1 AND @last_referenced_modify_date > @modify_date) ) BEGIN
SET @iterations = @iterations + 1;
BEGIN TRY
IF @type = 'V' BEGIN
EXECUTE sp_refreshview @name;
END ELSE BEGIN
EXECUTE sp_refreshsqlmodule @name;
END;
...