Script all views/functions/procedures in a Sql Server database in dependency order
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)
Answer
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.