Generate Table Scripts

Question

Generate Table Scripts

David.Poole

SSC Guru

Points: 76000
More actions
October 9, 2006 at 10:01 am

#170661

I want to generate scripts for the table structure of my database.
Is there a DLL call I can make to trigger the built in Enterprise Manager Script functionality?
I have a T-SQL script to do the scripting however I am worried about breaching the 8,000 character limit of a VARCHAR field.
LinkedIn Profile

Viewing 5 posts - 1 through 4 (of 4 total)

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

Charles Kincaid SSChampion Points: 13593 More actions · Answer 1

I'm in the enviable position of being both a developer and DBA. For CREATE TABLE you should not have much of a problem. We have to do our replication and synchronization stuff by hand. We have a stored procedure that returns a CREATE TABLE script for the entire structure. True, it does use temp tables to create the script but the big field is for the field name. As long as you don’t have field names longer than 8000 characters you should be just fine.

ATBCharles Kincaid

Brian Fenton SSC-Addicted Points: 429 More actions · Answer 2

Check out this article that I found here several months ago. I implemented it as a weekly job on our systems and it's been working quite well.

http://www.sqlservercentral.com/columnists/jreade/howtoscheduleasqlserverdatabasecreationscript.asp

Good luck!

Brian

Charles Kincaid SSChampion Points: 13593 More actions · Answer 3

Thanks Brian.

I had looked at that last week. Good article. Unfortunately SCPTXFR is not part of 2005. I tried to move it to my 2005 box but it is tied into a bunch of stuff on the original box.

This tool is part of the pacakge designed to upgrade SQL 6.5, or 7, to SQL 2000. As long as it works for you then cool. Expect disapointment when you migrate forward and this excellent looking tool is no longer available.

Looks like a great tool and I wish that Microsoft would roll out a new version.

ATBCharles Kincaid

debrucer1 SSCertifiable Points: 5366 More actions · Answer 4

That's a great tip! Thanks. I hope MS comes out with something for SMO. It would be nice to put this in place for all our 2000 DBs; but is questionable as to whether anybody would provide a charge number if it won't upgrade.

When you create it with object into individual files there are a lot of empty files on one of our busiest databases. Has anybody figured out what some of those file extentsion/objects are?

Anyway, thanks again for the link.

Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982