Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Get DDL for any SQL 2000 table


Get DDL for any SQL 2000 table

Author
Message
Lowell
Lowell
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24844 Visits: 39725
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!

Bill Nicolich
Bill Nicolich
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 543
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
Lowell
Lowell
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24844 Visits: 39725
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!

Iwas Bornready
Iwas Bornready
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12354 Visits: 885
Thanks for the script.
Grumpy DBA
Grumpy DBA
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6215 Visits: 1881
Iwas Bornready (5/24/2016)
Thanks for the script.


You do realize the last post was from 2009?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search