Post reply

Get DDL for any SQL 2000 table

  • July 12, 2009 at 6:18 pm

    #208328

    Comments posted to this topic are about the item Get DDL for any SQL 2000 table

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 28, 2009 at 7:08 am

    #1030992

    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

  • July 28, 2009 at 7:12 am

    #1030995

    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.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 24, 2016 at 7:06 am

    #1881074

    Thanks for the script.

  • May 24, 2016 at 7:08 am

    #1881079

    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)

You must be logged in to reply to this topic. Login to reply