Sql-server – Creating a view on a non-existing view

sql serverview

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

Best Answer

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.

Related Solutions

Configure Oracle user in Enterprise Manager to CREATE VIEW on any of his tables

I'm not sure if I'm understanding your question properly. I created a user with the ability to create views for his own schema.

SQL> create user view_user identified by password default tablespace users quota unlimited on users;

User created.

SQL> grant create session, create table, create view to view_user;

Grant succeeded.

SQL> connect view_user
Enter password: 
Connected.
SQL> CREATE TABLE Members (ID number(10), Name varchar2(100), Street_No varchar2(100), ZIP number(5), City varchar2(100));

Table created.

SQL> CREATE VIEW MembersNamesOnly AS SELECT ID, Name FROM Members;

View created.

SQL>

SQL Server – Create or Alter Stored Procedure Without Compiling

What you are referring to is deferred name resolution and is further explained here Deferred Name Resolution and Compilation.

Deferred name resolution can only be used when you reference nonexistent table objects. All other objects must exist at the time the stored procedure is created. For example, when you reference an existing table in a stored procedure you cannot list nonexistent columns for that table.

Basically, no you are not able to alter the parsers rules for what objects must exist at parse and compile time. I've previously had this issue when deploying SPs which reference linked servers and those linked servers did not have a valid connection. To work around it I updated the linked sever logic to be dynamic SQL.

Best Answer

Related Solutions

Configure Oracle user in Enterprise Manager to CREATE VIEW on any of his tables

SQL Server – Create or Alter Stored Procedure Without Compiling

Related Question