How do I use SMO to drop and recreate all views in a Database?

Question

I recently copied a MSSQL2000 DB to a MSSQL2008 DB. Afterwards I ran a script to update all the text/varchar fields in the tables to the same collation as the model db. However, all my copied views still are using the same collation as the previous db. The easiest way I found to fix this would be to have MS SQL Management Studio create the DROP/CREATE scripts for these views. However, certain views depend on other views so you need to make sure to DROP/CREATE these views in the proper order.

So my question is:

How would I create a script that traverses, depth first, the dependencies of each view and then on the return trip drops and creates each view?

I'm assuming that this will use SMO. I will be writing the script in C#.

Answer 1

in cases like this just run the script a bunch of times (SSMS f5), eventually all the child views will exist when they are used by a parent. You can tell when everything is good, as there will be no errors.

Answer 2

Can you not just iterate through and execute sp_refreshview dynamically? This will achieve the same result: updating the view for the new base table definition.

You aren't using WITH SCHEMABINDING (otherwise could not change tables) so dependency or depth does not matter.

Answer 3

Hey mate,
Try DBSourceTools. http://dbsourcetools.codeplex.com.
This utility uses SMO to script all database objects and data to disk (using DROP / CREATE) scripts.
It also automatically builds a dependancy tree and will re-create database objects in the required order.
Once you have scripted your 2000 DB to disk, create a "Deployment Target" database for your 2008 database.
You can then create a full set of patches to change / upgrade / rename all of your views.
DBSourceTools will re-build your database for you, and then apply all patches in order.
It's a reliable, repeatable method of version control for databases, and allows you to test and version control these patches.
When you are ready for release, simply send all of the patches to your DBA, and get him to run them in order. Have fun.