Script all views / functions / procedures in the Sql server database in the order of dependency

Question

Sql Server 2008 (and possibly most of the other versions): Management Studio has the option to “generate scripts”, which theoretically can contain a script a whole database with all objects (right-click, tasks, script generation). It works fine for most things, but when you use it to script all views / stored procedures / functions in the database, it generates a script that does not take into account dependencies between objects.

eg. If View A refers to function B, it will not necessarily first place function B in the script.

It takes a long time to unravel a large long script that is created in such a way that it is in order, which will work without errors.

There must be a better way. What is the best way to get around this, preferably without spending money? *

* (red gate ftw)

Answer 1

Unfortunately, the only quick and easy way to create such a script is to use third-party tools. They used Apex Script , but there are other tools, and Red Gate probably has its own version of this.

Other options:

• Running the script many times until everything is done
• Trying to create the correct order yourself using sys.dependants, which may not always work
• Come up with your own dependency algorithm, which is redundant ...

In previous versions of SQL Server, an error occurred in the sys dependency views. I remember reading about it when SQL 2008 was ready for release.

I don’t remember all the details, but it was something that the dependencies didn’t work correctly when the objects were deleted and re-created.

Answer 2

Here's an approach with poor people:

• Create a query based on sys.sql_dependencies, which are listed from bottom to top. That is, the list base is first objects, then objects on which depend on them, etc. This will give you the order in which to script your objects.
• Use powershell to then script those objects.