Generate SQL Script

Question

Generate SQL Script

lmod

SSC Veteran

Points: 246
More actions
August 16, 2004 at 6:42 pm

#108986

Hi guy's, I am developing app's for my web site and I have a database with (8) tables. I have to add about 4-5 more tables for the app that I am about to develop. My host previously allowed remote connectivity to MS SQL Server via App's like Enterprise Manager etc. but due to some miss use from some of their clients the shut down the com ports. When designing my db I used Enterprise Manager and Query Analyzer and I work well in that environment. Now I have to use a web based app called ASP.NET Enterprise Manager. This is ok for simple table creation but not good for anything else for a developer on my level. There is no way to develop a db diagram which is how I do most of my table relationships, also there is no way to create and view triggers. So I decided to install MSDE on my desktop so I can design my db in Enterprise Manager then when finished upload to my server which brings me to my problem. Enterprise Manager has a function "Generate SQL Script" which when ran it will generate the 'Create Table' script, script for all objects associated with a table. I was wondering is there a way to perform that same task with DML? And if so could someone give an example? I know it was a mistake by going this far with my project and not back up my SQL script ( I had a crash a few months ago), If I had I could just run my SQL script in Query Analyzer to recreate my db in MSDE so I can continue to develop. I could just redo everything in MSDE to build the test db but I want to ensure nothing is missing and I have an exact duplicate of my active db. Any help would be appreciated.

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

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

There's a nasty VB program at C:\Program Files\Microsoft SQL Server\80\Tools\DevTools\Samples\sqldmo\vb\SQLScripts on the server. If you can't get to that, there's an .EXE called "scptxfr.exe" and you can find out more about how to use it at http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q220/1/63.asp&NoWebContent=1

I believe that the only problem with the exe is that you don't get a choice as to what to script... you get the scripts for every object in the database... look at it this way... it saves lot's of time

For procedures, views, triggers and other "coded" objects, check out the SYSCOMMENTS table. The code text is stored in the TEXT column of that table. See Books on Line for more info on that table and how the code is segmented into 4k chunks.

If you have access to Query Analyzer, you can still generate scripts even quicker than you can from Enterprise Manager... press the F8 key, open the database,etc from the tree, right click on the object to generate a script for, and select how you want the script to be generated. You may want to set some of the options like to automatically generate a related If Exists.. Drop statement and the option to supress those bloody collation statements.

I think the scptsfr.exe comes with MSDE, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

Oh crud... I just checked some of the switches for the scptsfr.exe routine I told you about and I don't think it'll work because the login is always "SA". If they won't let you in with EM or QA, you probably won't have the SA password.

I think I'd try setting up the VB program just for ease of use. Otherwise, you need to get real good at using SYSOBJECTS, SYSCOMMENTS, SYSCOLUMNS, and SYSTYPES tables in SQL Server. They're not complicated... it's just a pain that you have to go through this...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

lmod SSC Veteran Points: 246 More actions · Answer 3

Thanks Jeff I will see if your suggestions are possible.

Ken Klaft-80806 Say Hey Kid Points: 693 More actions · Answer 4

I'll give you two items as food for thought.

1.) VB has a SQLDMO library available. Write your own SCRIPTXFR.EXE. I'm not a VB programmer but I was able to muddle through pretty easily. Surfing the net for SQLDMO.Transfer

2.) Have the ISP send you last night's backup of the database. If they don't have one, have them make one for you. Load the backup to your MSDE box and you have an exact copy of the database "over there"...

Cheers,

Ken

lmod SSC Veteran Points: 246 More actions · Answer 5

Thanks for the info. What I did was have my host run Gen SQL Script localy on my db and all table and objects and email me the script. Ran it is Query Analyzer and I was all set.