I finally got one of the harder unit tests for my SqlMigration tool passing.  The interesting thing is that I'm using NHibernate to populate my object graph of objects that describe the database.  I'm just using standard SQL queries that hit the INFORAMTION_SCHEMA tables.  Here's one of the tests:

[Test]

public void ShouldReturnAddressForeignKeys()

{

    ITable address = session.Get<Table>("Address");

    ITable state = session.Get<Table>("StateProvince");

    Assert.IsNotNull(address, "No address table returned");

    Assert.IsNotNull(state, "No state table returned");

    Assert.IsNotNull(address.Columns, "No address columns returned");

    Assert.AreEqual("FK_Address_StateProvince_StateProvinceID",

        address.Columns[4].ForeignKey.Name, "Incorrect FK name");

    Assert.AreEqual(address.Columns[4],

        address.Columns[4].ForeignKey.ForeignKeyColumn, "ForeignKeyColumn StateProvinceID is incorrect");

    Assert.AreEqual(state.Columns[0],

        address.Columns[4].ForeignKey.PrimaryKeyColumn, "PrimaryKeyColumn StateProvinceID is incorrect");

}

Unfortunately I just realized that you can have a compound primary key so I really ought to change ForeignKey.PrimaryKeyColumn to a list ForeignKey.PrimaryKeyColumn[0]...

Using NHibernate for this kind of work probably isn't the easiest solution, but once the mappings are right NHibernate takes care of correctly populating the object graph which isn't trivial because of all the bidirectional associations.  I've definitely learned a lot about NHibernate mappings and SQL Server's INFORMATION_SCHEMA views. NHibernate really is flexible and powerful and NHibernate makes loading a specific object from the database very easy and fluent.  The only "code" I had to write were the XML mapping files besides building a SessionFactory. 

It does seem that using NHibernate to directly query SQL Server is too time consuming development wise especially when other people have already figure this stuff out. If there were am easy way to generate NHibernate mapping files at runtime for a DB I could probably use NHibernate's rich meta data model instead of, or in addition too.  Another possibility is to use MyGeneration's data model or even SMO to populate my entities.

In some ways it seems like I'm reinventing SMO, but at least I have the source code to my version of SMO and my version doesn't have a hard dependency on SQL Server even if SMO has a lot more functionality.