Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Get DDL for any SQL 2000 table Expand / Collapse
Author
Message
Posted Sunday, July 12, 2009 6:18 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 12,903, Visits: 32,146
Comments posted to this topic are about the item Get DDL for any SQL 2000 table

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #751784
Posted Tuesday, July 28, 2009 7:08 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 12, 2013 1:09 PM
Points: 111, Visits: 541
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
Post #760714
Posted Tuesday, July 28, 2009 7:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 12,903, Visits: 32,146
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #760717
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse