IWETHEY v. 0.3.0 | TODO
1,095 registered users | 0 active users | 0 LpH | Statistics
Login | Create New User
IWETHEY Banner

Welcome to IWETHEY!

New MS SQL Server - Generate Scripts programmatically?

In Enterprise Manager, right click a DB, select "all actions", and "Generate Script". Result is the SQL code required to generate an SQL schema (optionally: complete to the point of including users, database options, etc.).

\r\n\r\n

Checking references, I don't find any equivalent automatic generation of same schema programmatically, using any stored procedures. There are a number of sites with instructions on how to create scripts which will do the same query for you though. Examples: [link|http://www.databasejournal.com/features/mssql/print.php/1442201|here], [link|http://www.microsoft.com/technet/treeview/default.asp?url=/technet/itcommunity/chats/trans/sql/sql1101.asp|here], [link|http://dbforums.com/archives/t160828.html|here], [link|http://dbforums.com/archives/t160828.html|here], and [link|http://forums.databasejournal.com/showthread.php?threadid=31326|here].

\r\n\r\n

Using sqsh, I have't got the script to run yet (still learning MS SQL Server, so I am probably futzing something up). Anyone know if I'm overlookign an existing functionality withing MS SQL Server before I invest the coding time into this?

\r\n\r\n

BTW - sqsh is pretty cool stuff. But it's [link|http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=194065|got an authentication bug] when using ~/.sqshrc to supply password.

--\r\n
Karsten M. Self [link|mailto:kmself@ix.netcom.com|kmself@ix.netcom.com]\r\n
[link|http://kmself.home.netcom.com/|http://kmself.home.netcom.com/]\r\n
What part of "gestalt" don't you understand?\r\n
[link|http://twiki.iwethey.org/twiki/bin/view/Main/|TWikIWETHEY] -- an experiment in collective intelligence. Stupidity. Whatever.\r\n
\r\n
   Keep software free.     Oppose the CBDTPA.     Kill S.2048 dead.\r\n[link|http://www.eff.org/alerts/20020322_eff_cbdtpa_alert.html|http://www.eff.org/alerts/20020322_eff_cbdtpa_alert.html]\r\n
New Define not run
I can generate and run, BUT, you must make sure
to generate in regular ASCII, not unicode. Now that
gvim handle unicode, I could not figure out why it
did not work. Until I hex dumped the file.

Also, remove the stupid "GO"s and change them to a semi-colon
at the end of each command.

Also, no damn feedback.

When it works, it won't tell you.

I ended up rerunning about 5 times before I realized it worked!!

Also, watch out for tab delimited NL terminated data when loading via DTS.
The BASTARDS shove the final NL into the final field if it is empty.
CR/LF data works fine.

It's been an evil MS-SQL week for me.
New I generally do it the other way.
I keep the source to generate the stored procedures, tables, views, functions, etc... in the version control system. The scripts are much cleaner and easier to control than the auto-generated variety. Too many SQL Server DBA's tend to use the Server itself as the code repository (a bad idea from a maintenance perspective).

Just one note if you are going to generate the scripts => remember to drop the objects in the reverse order that you created them - otherwise you run into object dependency problems (e.g. can't delete a table that is currently acting as a FK). I've noticed in the past that the auto-generated scripts from SQLServer did not always maintain the correct order of drop and create - meaning you had a fairly arduous task trying to get things manually in the right order. Perhaps they've fixed that on the later versions (it's been awhile since I've used the feature).


New No experience with this problem in MS-SQL, but
can't you "drop cascade" to take care of any dependancies?

Never mind. Oracle great, SQL/Server sucks.
You need to exec sp_depends and drop the results of that. Of course, for each of those, you would need to do the same thing.

Recursion at it finest.
New An error list might be helpful.
Using sqsh, I have't got the script to run yet (still learning MS SQL Server, so I am probably futzing something up).

You getting back any error text when you "try to run it"?
bcnu,
Mikem

The soul and substance of what customarily ranks as patriotism is moral cowardice and always has been...We have thrown away the most valuable asset we had-- the individual's right to oppose both flag and country when he (just he, by himself) believed them to be in the wrong. We have thrown it away; and with it all that was really respectable about that grotesque and laughable word, Patriotism.

- Mark Twain, "Monarchical and Republican Patriotism"
     MS SQL Server - Generate Scripts programmatically? - (kmself) - (4)
         Define not run - (broomberg)
         I generally do it the other way. - (ChrisR) - (1)
             No experience with this problem in MS-SQL, but - (broomberg)
         An error list might be helpful. - (mmoffitt)

Completely free of Microsoft Smart Tags!
73 ms