Compare and synchronize the views and inline functions in SQL Server databases

Views & Inline Functions

Compare and synchronize Views & Inline Functions.

Parameters of comparison and synchronization the views and inline-functions in SQL Server databases, including the rebuild of all views, ANSI_NULLS control, updating metadata with sp_refreshview and sp_refreshsqlmodule based on the dependencies between the objects in the structure
Create

Create new ones, if they do not exist in the target database.

Rebuild

Rebuild (drop and create) existing ones to remove the differences.

Drop

Drop existing ones, if they do not exist in the template 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. It is highly recommended to always use this option in the comparison.

Append refresh of Views & Inline Functions

Append to the synchronization script the special algorithm to refresh Views & Inline Functions in the particular order.

Parameters of updating views metadata and inline-functions in SQL Server databases by call sp_refreshview and sp_refreshsqlmodule
Refresh by altered dependencies

Refresh all objects that are referencing to the altered or rebuilt objects which were modified since the preparatory script was running.

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;
...