Exporting stored procedures

Hello!
I look for a way to export the stored procedures from a database
automatically. The GUI way is "generate SQL script", but I want to do it via
script, or an API.
Thanks!
RoeeIt is all in the SQL-DMO API.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Roee Friedman" <roee@.monosphere.com> wrote in message
news:bnl2d1$12c9cd$1@.ID-200860.news.uni-berlin.de...
> Hello!
> I look for a way to export the stored procedures from a database
> automatically. The GUI way is "generate SQL script", but I want to do it via
> script, or an API.
> Thanks!
> Roee
>|||Hi,
You can use DTS packages to copy objects to destination.
The task name is "Copy SQL Server Objects Task" , in the copy tab you can
select all stored procedures.
Thanks
Hari
MCDBA
"Roee Friedman" <roee@.monosphere.com> wrote in message
news:bnl2d1$12c9cd$1@.ID-200860.news.uni-berlin.de...
> Hello!
> I look for a way to export the stored procedures from a database
> automatically. The GUI way is "generate SQL script", but I want to do it
via
> script, or an API.
> Thanks!
> Roee
>|||"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:eOMJaLSnDHA.1728@.TK2MSFTNGP09.phx.gbl...
> Hi,
> You can use DTS packages to copy objects to destination.
> The task name is "Copy SQL Server Objects Task" , in the copy tab you can
> select all stored procedures.
Although this will only work if the source and destination are both SQL
Servers
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003|||Here's an example of scripting all the stored procedures in a database with
Perl and SQL-DMO. Save the following code in a file, say, named scriptSP.pl:
################## Being script
# tested with ActivePerl 5.6.1, Binaray Build 631
use strict;
use Getopt::Std;
use Win32::OLE 'in';
use Win32::OLE::Const 'Microsoft SQLDMO'; # import the SQL-DMO symbolic
constants
my %opts;
getopts('S:d:o:', \%opts); # Get the command line arguments
my ($serverName, $dbName, $output) = ($opts{S}, $opts{d}, $opts{o});
# Check whether the required command line arguments are specified
(defined $serverName && defined $dbName && defined $output)
or printUsage();
# Create a SQLDMO SQLServer object
my $server = Win32::OLE->new('SQLDMO.SQLServer') or
die "***Err: could not create SQLDMO object.\n";
$server->{LoginSecure} = 1; # trusted connection
# Connect to the SQL instance via trusted connection
$server->connect($serverName);
! Win32::OLE->LastError() or
die "***Err: SQLDMO could not connect to $serverName.\n";
# Get the SQLDMO Database object
my $db = $server->Databases($dbName);
# Loop through the StoredProcedures collection to script each stored
procedure
foreach my $obj (in($server->Databases($dbName)->StoredProcedures())) {
$obj->Script(SQLDMOScript_Default | SQLDMOScript_AppendToFile |
SQLDMOScript_IncludeHeaders | SQLDMOScript_Drops |
SQLDMOScript_ToFileOnly,
$output); # the SQL script goes to this
file
}
# Clean up
$server->disconnect();
$server->DESTROY();
sub printUsage {
print << '--Usage--';
Usage:
cmd>perl scriptSP.pl -S <SQL instance> -d <database name> -o <output
file>
--Usage--
exit;
} # printUsage
################# End script
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Roee Friedman" <roee@.monosphere.com> wrote in message
news:bnl2d1$12c9cd$1@.ID-200860.news.uni-berlin.de...
> Hello!
> I look for a way to export the stored procedures from a database
> automatically. The GUI way is "generate SQL script", but I want to do it
via
> script, or an API.
> Thanks!
> Roee
>