Comments posted to this topic are about the item Get DDL for any SQL 2000 table
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.
Bill Nicolich: www.SQLFave.com.
Daily tweet of what's new and interesting: AppendNow
thanks for the feedback Bill;
I had initially tried to use the INFORMATION_SCHEMA views to build a script like this...the problem is, those views do not contain all the information. which column has the identity() property for example, and you cannot find things like the seed and increment of an identity column either, to name just one example i faced.
Thanks for the script.
Iwas Bornready (5/24/2016)
Thanks for the script.
You do realize the last post was from 2009?
Viewing 5 posts - 1 through 4 (of 4 total)