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.

Josh
  • 8,219
  • 13
  • 76
  • 123

1 Answers1

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.

Community
  • 1
  • 1
mellamokb
  • 56,094
  • 12
  • 110
  • 136
  • 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? – Josh Jul 15 '11 at 22:30
  • I can give you credit for the answer, but just wanted a definitive "no, it can't be done" – Josh Jul 20 '11 at 18:50
  • @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. – Stefan Steiger Nov 14 '17 at 10:59