Compare and synchronize views and inline functions of SQL Server databases

Comparison and synchronization options for views and inline functions of SQL Server databases, including recreation of all views, ANSI_NULLS control

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.

Updating views and inline functions metadata with sp_refreshview and sp_refreshsqlmodule based on schema object dependencies

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