Thanks Lowell for sharing. Lately there have been articles and discussions about test-driven database development where I think a script like yours should be considered. For instance, Andy Leonard is doing a series of articles on TDD and is currently on url=http://www.sqlservercentral.com/articles/Test-Driven+Development/67487/]PART 4[/url].

One concept of TDD that's been discussed is the idea of creating a test for the existence of a database object like a table before creating it. So far, the code examples only demonstrate a check for the existence of the table itself - not the columns, data types, nullability, constraints and so forth. Without the details, the test holds little meaning.

The problem is, adding meaning to the test by checking for the object details exposes the practicality problem with trying to apply the "test first" technique while developing database entities. Can we really be expected to create a test for all the attributes of a table before we build it? I'd argue that too much impedance is created with that approach. The Oslo and MSchema development pattern flows in the direction of create entities iteratively with many changes up-front, and then save all the meta-data immediately thereafter. I think we need to be able to accommodate that.

What I've suggested is to use a "create the existence test immediately after" technique. I think it's more practical to go ahead and create the table (or other object) - and then right after that, create the test to check for all the attributes of the object.

I think the "create table" script that you've made is a step in that direction. The output of any such script needs to be 100% complete and 100% reliable. It should probably be based on INFORMATION_SCHEMA - which is the ANSI standard for SQL describing itself.