Creating a view on a non-existing view

Question

I have a whole bunch of views that I need to create. Many of these views depend on other views.

If I create a view that depends on a view not yet created, I get an Invalid Object error.

Rather than go through the whole lot and work out the dependencies and then deal with creating them all in the correct order, is there a way I can just switch this checking off until all the views are created?

It is possible to delete a dependant view after the view is installed, so it is possible for the database to be in a state where a view exists that depends on a non-existent object. I need it to accept being in this state during creation..

• Gaius: Oracle has CREATE FORCE VIEW for exactly this use case, but I don't believe SQL Server has any equivalent.
• Mongus Pong: @Gaius - it seems SQL Server only has an equivalent for Stored Procedures, not Views.

Answer 1

If you have access to adatabase that the views exist within you can query sys.views to determine the order in which they where created. Query below should help with this, add a predicate to filter the list if it helps.

SELECT v.name 
FROM sys.views v
ORDER BY v.create_date

Answer 2

Another solution for the pathologically lazy - instead of CREATE use CREATE OR ALTER. Run the view scripts repeatedly until there are no Invalid Object errors.

In each iteration views without dependencies, and those whose dependencies were satisfied in the previous iteration, will succeed. The OR ALTER bit accommodates those object which were created in a previous round. Thus more object exist after each round until all are in place.

SQL Server 2016+.

Answer 3

To my knowledge it's not possible to create a view that is missing objects referenced in that view.

Why is it such a pain to know what the dependencies are on your views? You are CREATING them, right?

Do they already exist in another database? If so you can use SSMS or SMO to script them out in dependency order and bypass this issue.

• Mongus Pong: Darn it.. Yeah its just sheer laziness / efficiency (depending on how you look at it).
• Mike Sherrill 'Cat Recall': tsort is your friend.