Script out objects

[neo]

Is there any command to script out tables/stored procedures?
I want to make a process to script out tables every night.

thanks,

[Simon Hayes]

"neo" <se*******@hotmail.com> wrote in message
news:15**************************@posting.google.c om...

Is there any command to script out tables/stored procedures?
I want to make a process to script out tables every night.

thanks,

There's no TSQL command to do this, but you can use the SQLDMO interface to
generate the scripts. Although you could do it from a procedure using
sp_OACreate, it would probably be better to write an external script in your
preferred scripting language - VBScript, Perl, etc. - because it will be
much easier to work with the output files.

Simon

[neo]

any sample code in perl or VBScript?

Thanks,

[Simon Hayes]

se*******@hotmail.com (neo) wrote in message news:<15**************************@posting.google. com>...

any sample code in perl or VBScript?

Thanks,

Here's a snippet of VBScript (untested) to script all the tables in a
DB - check the docs for the constants available with the Script
method:

Set oSQL = WScript.CreateObject("SQLDMO.SQLServer2")
oSQL.Name = "MyServer"
oSQL.LoginSecure = True
oSQL.Connect

'Script all tables
For Each oTable In oSQL.Databases("MyDatabase").Tables
If Not oTable.SystemObject Then
oTable.Script 4 Or 262144 Or 520093696 Or 131072 Or 2 Or 64,
"C:\OutputFolder\" + oTable.Name + ".sql"
End If
Next
Simon