4

Sql Server 2008 (and probably most other versions): Management Studio has a 'generate scripts' option that can in theory script a whole database with all objects (Right click, tasks, Generate Scripts). It works OK for most things but when you use it to script all views/stored procedures/functions in a database, it generates a script that does not take account of dependencies between the objects.

e.g. If View A references Function B it won't necessarily put Function B in the script first.

It takes a long time to untangle the great long script that gets produced so that it is in an order that will run without errors.

There must be a better way. Whats the best way to get round this, preferably without spending any money?*

* (red gate ftw)

Germstorm
  • 9,709
  • 14
  • 67
  • 83
codeulike
  • 22,514
  • 29
  • 120
  • 167
  • 1
    just run your big script multiple times (until it runs with no errors) – RacerX Apr 04 '11 at 17:47
  • 1
    I just tried it with SQL Server 2008 Management Studio, and it did create the objects in the correct order. (To simplify things, the foreign keys were all created at the end, though.) I have never had a problem with this. Does your script have circular dependencies, or something? – Jeffrey L Whitledge Apr 04 '11 at 17:49
  • @Jeffrey - it seems it does work sometimes, but not dependably. I get the impression that sql loses track of dependencies within the database when objects have changed a lot. – codeulike Apr 04 '11 at 20:37

2 Answers2

3

Unfortunately, the only quick and easy way to create such a script is using some third party tools. We’re using Apex Script but there are also other tools out there and Red Gate probably has its own version of this.

Other options are:

  • Running the script many times until everything is executed
  • Trying to create correct order yourself using sys.dependancies which may not work always
  • Coming up with your own dependency algorithm which is an overkill …

There was a bug in sys dependencies views in previous versions SQL Server. I remember reading about it when SQL 2008 was about to be released.

I don’t remember all the detail but it was something about dependencies not working correctly when objects were dropped and re-created.

Logan Merrit
  • 131
  • 3
1

Here's a poor-man's approach:

  1. Craft a query based on sys.sql_dependencies that lists from the bottom up. That is, list base objects first, then the objects upon which those depend, etc. This will give you the order in which to script your objects.
  2. Use powershell to then script those objects out.
Ben Thul
  • 31,080
  • 4
  • 45
  • 68
  • Is sys.sql_dependencies 100% dependable? – alphadogg Aug 31 '12 at 02:47
  • Not at all. Records are placed in that system view when an object is created and are not necessarily kept up to date as the situation evolves. For instance: try creating a table, creating a view that references the table, then drop and re-create the table. Before and after you drop the table, note what dependency is noted in sys.sql_dependencies. – Ben Thul Aug 31 '12 at 08:31