3

I'm creating a build script for a database and building the views in alphabetical order. An issue I've run into is that a view is created before a view that is referenced in the definition. The referenced view is created later in the script. Is there a command I can use that would create a view without validating, i.e. ignore the dependencies?

Note: Stored Procedures have the concept of "deferred name resolution" but I don't see a way of using this for views.

Improve this question
2
  • 1
    Why not just create them in the correct order? Commented Jul 15, 2011 at 21:53
  • 2
    I want an automated script that doesn't have to contain logic for dependencies. Commented Jul 15, 2011 at 22:04

1 Answer 1

Reset to default
2

To add to @Joe Stefanelli's comment, there are many ways to script SQL objects in the correct order of dependency.

  1. Listing the dependency order from the SSMS UI.
  2. Use sp_depends to find dependency order, or using built-in dependency info tables.
  3. Generate the script using the SSMS Tasks -> Generate Scripts... wizard, which can actually be automated using tools like Scriptio or built-in .NET SMO Library.

Another poor man's solution is just to run the script as many times as you have layers of dependencies. Existing objects would be ignored, and each execution would create more objects that depend on earlier-created objects. I would not recommend this solution if at all possible to find something more direct, as mentioned in the list above.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

We have all our database objects in source control (TFS) with an individual SQL file per object. Each file drops the object if it exists then creates the object. I have a script that merges these into one script. I'm trying to build a process that supports this setup without having to manually adjust the build script. Is there a command that tells SQL Server to ignore dependencies on creation?
I can give you credit for the answer, but just wanted a definitive "no, it can't be done"
@mellamokb: The poor man's solution assumes that the scripts can be run multiple times (instead of just once), which usually is NOT the case, because SQL-scripts are NOT idempotent by default - and neither are the scripts generated by SSMS.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.