Hi,
Please guide me in generating sql scripts for table, with the drop
statements in it, from the management studio of sql 2005, Currently only
create statement is generating, and drop statement is not there in it.
Rupesh Mondal.Make sure you have updated Management Studio till SP2 and configure this (Tools, Options,
Scripting).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
news:D3D0A2E4-87F3-432A-8614-3C146FB95243@.microsoft.com...
> Hi,
> Please guide me in generating sql scripts for table, with the drop
> statements in it, from the management studio of sql 2005, Currently only
> create statement is generating, and drop statement is not there in it.
> Rupesh Mondal.|||But Mr.Tobor which option to set true in the scripting option, inorder to get
the drop statement in it, before the create statement.Please reply me ASAP
Rupesh Mondal.
"Tibor Karaszi" wrote:
> Make sure you have updated Management Studio till SP2 and configure this (Tools, Options,
> Scripting).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
> news:D3D0A2E4-87F3-432A-8614-3C146FB95243@.microsoft.com...
> > Hi,
> >
> > Please guide me in generating sql scripts for table, with the drop
> > statements in it, from the management studio of sql 2005, Currently only
> > create statement is generating, and drop statement is not there in it.
> >
> > Rupesh Mondal.
>
>|||Oops, by error. I was certain this option was there but it seems it isn't. The closest one I can
find is the "Include IF NOT EXIST clause" which is a bit different.
I think this option is available at the SMO level, but that would require you to code your own
generate script application.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
news:FBD71B40-7CB5-49C5-B9E8-6DD298E4385D@.microsoft.com...
> But Mr.Tobor which option to set true in the scripting option, inorder to get
> the drop statement in it, before the create statement.Please reply me ASAP
> Rupesh Mondal.
> "Tibor Karaszi" wrote:
>> Make sure you have updated Management Studio till SP2 and configure this (Tools, Options,
>> Scripting).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
>> news:D3D0A2E4-87F3-432A-8614-3C146FB95243@.microsoft.com...
>> > Hi,
>> >
>> > Please guide me in generating sql scripts for table, with the drop
>> > statements in it, from the management studio of sql 2005, Currently only
>> > create statement is generating, and drop statement is not there in it.
>> >
>> > Rupesh Mondal.
>>|||here's an open source app that will generate scripts - it would be a
starting point for you if you go that route:
http://www.codeplex.com/scriptdb
On Apr 19, 12:59 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Oops, by error. I was certain this option was there but it seems it isn't. The closest one I can
> find is the "Include IF NOT EXIST clause" which is a bit different.
> I think this option is available at the SMO level, but that would require you to code your owngeneratescriptapplication.
> --
> Tibor Karaszi,SQLServer MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Rupesh Mondal" <RupeshMon...@.discussions.microsoft.com> wrote in message
> news:FBD71B40-7CB5-49C5-B9E8-6DD298E4385D@.microsoft.com...
> > But Mr.Tobor which option to set true in the scripting option, inorder to get
> > the drop statement in it, before the create statement.Please reply me ASAP
> > Rupesh Mondal.
> > "Tibor Karaszi" wrote:
> >> Make sure you have updated Management Studio till SP2 and configure this (Tools, Options,
> >> Scripting).
> >> --
> >> Tibor Karaszi,SQLServer MVP
> >>http://www.karaszi.com/sqlserver/default.asp
> >>http://sqlblog.com/blogs/tibor_karaszi
> >> "Rupesh Mondal" <RupeshMon...@.discussions.microsoft.com> wrote in message
> >>news:D3D0A2E4-87F3-432A-8614-3C146FB95243@.microsoft.com...
> >> > Hi,
> >> > Please guide me in generatingsqlscripts for table, with the drop
> >> > statements in it, from the management studio ofsql2005, Currently only
> >> > create statement is generating, and drop statement is not there in it.
> >> > Rupesh Mondal.
Showing posts with label statements. Show all posts
Showing posts with label statements. Show all posts
Friday, March 23, 2012
Generating scripts in SQL 2005
In SQL 2000 EM you were able to generate a script that only contained GRANT statements, (by unchecking the CREATE & DROP commands on the 2nd tab, and selecting Object Permissions on the 3rd tab). From what I can see in SQL 2005 MS you can't do this.
All that you can seem to do is generate a sciprt containing all the CREATE statements with the GRANT statement(s) after each object.
This is a bit of a pain as I want to script all the object permissions, without having to wade through hundreds of CREATE TABLE and CREATE PROCEDURE statements.
How can get just a script of GRANT statements?
Thanks.
It is not possible to generate scripts for object permissions through Management Studio like you could in Enterprise Manager. However, you can write a small SMO program to get the information the you need. This example should get you started on how to generate GRANT scripts for stored procedures.
ObjectPermissionInfo[] a = new ObjectPermissionInfo[5];
Database db = srv.Databases["pubs"];
foreach (StoredProcedure sp in db.StoredProcedures)
{
a = sp.EnumObjectPermissions();
foreach (ObjectPermissionInfo op in a)
{
Console.WriteLine("GRANT " + op.PermissionType.ToString() + " ON " + op.ObjectName.ToString() + " TO " + op.Grantee.ToString());
}
}
~Peter
|||Thanks for answering, I'll try what you suggest.Monday, March 19, 2012
Generating Create Alter Drop Statements
Im using SQL Server 2005 Standard and am trying to get T-SQL to do something that can be done easily through the UI, Genernate SQL Scripts for Create Alter Drop for objects. In the SQL manager its as easy a right clicking a DB Object and going to tasks.
Im wondering if there is a way of doing that through T-SQL.
Thanks in advance!
There is no easy way to do the same from TSQL. Best is to use SMO or SSIS to script out the objects. You could use the built-in metadata function OBJECT_DEFINITION or sp_helptext system stored procedure or query system catalog views. But you will have to code lot of the logic to determine dependencies, generate drop statements or ALTER TABLE etc. So it is best to use one of the methods above.Friday, March 9, 2012
generate update statements for existing data
Does anyone know how to write scripts for generating update statements for
existing data?
I found a stored procedure online that generates INSERT statements for a
given table, I was wondering if anyone has worked on a UPDATE generatorMike
What do you mean by "generating update"?
Do you want to build a script that does updateting?
declare @.sql varchar(400)
set @.sql='update '+ @.tablename+' set col'..........
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:9E10B2B9-80CC-4E50-9E34-5865A0C3BA1D@.microsoft.com...
> Does anyone know how to write scripts for generating update statements for
> existing data?
> I found a stored procedure online that generates INSERT statements for a
> given table, I was wondering if anyone has worked on a UPDATE generator|||I have a tables with about 1000 records and if I make changes I would like t
o
generate script (UPDATE scripts) for the existing data that I could run on
another server or ship it to a client with the latest changes instead of a
restore.
there is a good insert script available at
http://vyaskn.tripod.com/code.htm#inserts , I am looking for something
similar for UPDATE
"Uri Dimant" wrote:
> Mike
> What do you mean by "generating update"?
> Do you want to build a script that does updateting?
>
> declare @.sql varchar(400)
> set @.sql='update '+ @.tablename+' set col'..........
>
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:9E10B2B9-80CC-4E50-9E34-5865A0C3BA1D@.microsoft.com...
>
>|||Mike
Do you consider using Replication/Triggers ? It will be much better than
generating SQL script.
If you use SQL Server 2005 you can create a SNAPSHOP database ( ah i forgot
, it should be on the same server),sorry.
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:00EF43C1-EBF7-4422-99D0-A1979F3500A0@.microsoft.com...
>I have a tables with about 1000 records and if I make changes I would like
>to
> generate script (UPDATE scripts) for the existing data that I could run on
> another server or ship it to a client with the latest changes instead of a
> restore.
> there is a good insert script available at
> http://vyaskn.tripod.com/code.htm#inserts , I am looking for something
> similar for UPDATE
> "Uri Dimant" wrote:
>|||Mike wrote:
> I have a tables with about 1000 records and if I make changes I would like
to
> generate script (UPDATE scripts) for the existing data that I could run on
> another server or ship it to a client with the latest changes instead of a
> restore.
> there is a good insert script available at
> http://vyaskn.tripod.com/code.htm#inserts , I am looking for something
> similar for UPDATE
>
http://www.red-gate.com/products/SQ...mpare/index.htm
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Try www.sqlscripter.com to generate data scripts. All types are supported
(Insert, Update, Delete).
It's free.
Thomas
"Mike" wrote:
> Does anyone know how to write scripts for generating update statements for
> existing data?
> I found a stored procedure online that generates INSERT statements for a
> given table, I was wondering if anyone has worked on a UPDATE generator
existing data?
I found a stored procedure online that generates INSERT statements for a
given table, I was wondering if anyone has worked on a UPDATE generatorMike
What do you mean by "generating update"?
Do you want to build a script that does updateting?
declare @.sql varchar(400)
set @.sql='update '+ @.tablename+' set col'..........
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:9E10B2B9-80CC-4E50-9E34-5865A0C3BA1D@.microsoft.com...
> Does anyone know how to write scripts for generating update statements for
> existing data?
> I found a stored procedure online that generates INSERT statements for a
> given table, I was wondering if anyone has worked on a UPDATE generator|||I have a tables with about 1000 records and if I make changes I would like t
o
generate script (UPDATE scripts) for the existing data that I could run on
another server or ship it to a client with the latest changes instead of a
restore.
there is a good insert script available at
http://vyaskn.tripod.com/code.htm#inserts , I am looking for something
similar for UPDATE
"Uri Dimant" wrote:
> Mike
> What do you mean by "generating update"?
> Do you want to build a script that does updateting?
>
> declare @.sql varchar(400)
> set @.sql='update '+ @.tablename+' set col'..........
>
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:9E10B2B9-80CC-4E50-9E34-5865A0C3BA1D@.microsoft.com...
>
>|||Mike
Do you consider using Replication/Triggers ? It will be much better than
generating SQL script.
If you use SQL Server 2005 you can create a SNAPSHOP database ( ah i forgot
, it should be on the same server),sorry.
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:00EF43C1-EBF7-4422-99D0-A1979F3500A0@.microsoft.com...
>I have a tables with about 1000 records and if I make changes I would like
>to
> generate script (UPDATE scripts) for the existing data that I could run on
> another server or ship it to a client with the latest changes instead of a
> restore.
> there is a good insert script available at
> http://vyaskn.tripod.com/code.htm#inserts , I am looking for something
> similar for UPDATE
> "Uri Dimant" wrote:
>|||Mike wrote:
> I have a tables with about 1000 records and if I make changes I would like
to
> generate script (UPDATE scripts) for the existing data that I could run on
> another server or ship it to a client with the latest changes instead of a
> restore.
> there is a good insert script available at
> http://vyaskn.tripod.com/code.htm#inserts , I am looking for something
> similar for UPDATE
>
http://www.red-gate.com/products/SQ...mpare/index.htm
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Try www.sqlscripter.com to generate data scripts. All types are supported
(Insert, Update, Delete).
It's free.
Thomas
"Mike" wrote:
> Does anyone know how to write scripts for generating update statements for
> existing data?
> I found a stored procedure online that generates INSERT statements for a
> given table, I was wondering if anyone has worked on a UPDATE generator
Generate Store Procedure ?
I have a table in database !
I want to generate store procedure from that table by using tool of SQL server 2000( which include some statements : insert,delete, ....)
how can i do that ? thank you very much .
I want to generate store procedure from that table by using tool of SQL server 2000( which include some statements : insert,delete, ....)
how can i do that ? thank you very much .
Try the link below and test drive the tool it was created by the best T-SQL programmers before the BMC bought them. Hope this helps.
http://www.bmc.com/products/proddocview/0,,0_0_0_8739,00.html
Friday, February 24, 2012
Generate insert statement for table
I have data in a table that I wish to transfer to a remote system (both SQL
2000).
Is there an easy way to generate a series of insert statements from my
database so I can transfer this text file to my remote system. If possible
I like to be able to exclude data with a where statement.
Regards
Jeff
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.564 / Virus Database: 356 - Release Date: 19/01/2004http://vyaskn.tripod.com/code.htm#inserts
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:Oxo8g173DHA.540@.tk2msftngp13.phx.gbl...
SQL
possible
The script deals with all data types, except image and
sql_variant, but including the user defined data types.
#############Begin script###########
# Created by: Linchi Shea
use strict;
use Data::Dumper;
use Win32::ODBC;
use Getopt::Std;
my %opts;
getopts('S:d:t:', \%opts);
my ($server, $dbName, $tbName) = ($opts{S}, $opts{d}, $opts
{t});
(defined $server && defined $dbName && defined $tbName) or
printUsage();
Main: {
my $connStr = "Driver={SQL Server};Server=$server;" .
"Database=$dbName;Trusted_Connection=yes";
my $conn = new Win32::ODBC($connStr) or
die "***Err: " . Win32::ODBC::Error();
my ($columnRef, $attribRef) = getColumnProperties
($tbName, $conn);
my $sql = constructINSERT($columnRef, $attribRef,
$conn);
print $sql;
$conn->Close();
} # Main
############################
sub getColumnProperties {
my($tbName, $conn) = @._;
my @.columns;
my %attrib;
if (! $conn->Sql("select * from $tbName where 1=2") ) {
1 while $conn->FetchRow();
# first get the data type for each column
my @.fields = $conn->FieldNames();
%attrib = $conn->ColAttributes($conn-
# in case the data type is user defined, we need
# the real data type to help us decide how to handle
# the retrieved data in an INSERT statement
foreach my $field (@.fields) {
if (! $conn->Sql("sp_help '$attrib{$field}'") )
{
while($conn->FetchRow()) {
my ($type) = $conn->Data("Storage_type");
$attrib{$field} = $type;
}
}
if ($attrib{$field} =~ /^(image|sql_variant)$/i) {
die "***Err: data type $attrib{$field} not
supported.\n";
}
push @.columns, $field if lc($attrib{$field})
ne 'timestamp';
}
}
else {
die "***Err: failed to run select * from $tbName
where 1=2.\n";
}
return (\@.columns, \%attrib);
} # getColumnProperties
########################
sub constructINSERT {
my($columnRef, $attribRef, $conn) = @._;
(scalar @.$columnRef && scalar %$attribRef) or
die "Err: \$columnRef or \$attribRef is empty.\n";
my $sql;
if (! $conn->Sql("select * from $tbName") ) {
# now get the data values for each row
while ($conn->FetchRow()) {
$sql .= "INSERT $tbName (" . join(',',
@.$columnRef) . ")\n";
my @.values = ();
my %data = $conn->DataHash();
# decide how to handle the VALUE clause of the
INSERT
foreach my $column (@.$columnRef) {
# the values of these data types can be used
as is
if ($attribRef->{$column}
=~ /int|smallint|bigint|tinyint|
bit|decimal|numeric|money|
smallmoney|float|real
/ix) {
if (defined $data{$column}) {
push @.values, $data{$column};
}
else {
push @.values, 'NULL';
}
}
# the values of these types must be quoted
with a pair of
# single quotation marks
elsif ($attribRef->{$column}
=~ /datetime|smalldatetime|
char|varchar|nchar|nvarchar|
text|ntext|uniqueidentifier
/ix) {
if (defined $data{$column}) {
$data{$column} =~ s/'/''/g;
push @.values, "'$data{$column}'";
}
else {
push @.values, 'NULL';
}
}
# the binary data must be converted to a HEX
string format
elsif ($attribRef->{$column}
=~ /binary|varbinary
/ix) {
if (defined $data{$column}) {
push @.values, '0x' . unpack("H*", $data
{$column});
}
else {
push @.values, 'NULL';
}
}
else {
print "***Assert: invalid code path. Skip
this row.\n";
next;
}
}
$sql .= "VALUES (" . join(',', @.values) . ")\n";
}
}
return $sql;
} # construtcINSERT
###################
sub printUsage {
print << '--Usage--';
Usage:
cmd>perl GeneratedataInserts.pl -S <SQL server or
instance>
-d <database name>
[ -t <table name> ]
--Usage--
exit;
} # printUsage
##############End script###############
remote system (both SQL
statements from my
system. If possible
19/01/2004
1. creating a separate db
2. put the data in a table in that db
3. backup that db
4. do the reverse on the remove server.
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:Oxo8g173DHA.540@.tk2msftngp13.phx.gbl...
SQL
possible
2000).
Is there an easy way to generate a series of insert statements from my
database so I can transfer this text file to my remote system. If possible
I like to be able to exclude data with a where statement.
Regards
Jeff
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.564 / Virus Database: 356 - Release Date: 19/01/2004http://vyaskn.tripod.com/code.htm#inserts
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:Oxo8g173DHA.540@.tk2msftngp13.phx.gbl...
quote:
> I have data in a table that I wish to transfer to a remote system (both
SQL
quote:
> 2000).
> Is there an easy way to generate a series of insert statements from my
> database so I can transfer this text file to my remote system. If
possible
quote:|||Here's a PERL script for generating SQL INSERT statements.
> I like to be able to exclude data with a where statement.
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.564 / Virus Database: 356 - Release Date: 19/01/2004
>
The script deals with all data types, except image and
sql_variant, but including the user defined data types.
#############Begin script###########
# Created by: Linchi Shea
use strict;
use Data::Dumper;
use Win32::ODBC;
use Getopt::Std;
my %opts;
getopts('S:d:t:', \%opts);
my ($server, $dbName, $tbName) = ($opts{S}, $opts{d}, $opts
{t});
(defined $server && defined $dbName && defined $tbName) or
printUsage();
Main: {
my $connStr = "Driver={SQL Server};Server=$server;" .
"Database=$dbName;Trusted_Connection=yes";
my $conn = new Win32::ODBC($connStr) or
die "***Err: " . Win32::ODBC::Error();
my ($columnRef, $attribRef) = getColumnProperties
($tbName, $conn);
my $sql = constructINSERT($columnRef, $attribRef,
$conn);
print $sql;
$conn->Close();
} # Main
############################
sub getColumnProperties {
my($tbName, $conn) = @._;
my @.columns;
my %attrib;
if (! $conn->Sql("select * from $tbName where 1=2") ) {
1 while $conn->FetchRow();
# first get the data type for each column
my @.fields = $conn->FieldNames();
%attrib = $conn->ColAttributes($conn-
quote:
>SQL_COLUMN_TYPE_NAME, @.fields);
# in case the data type is user defined, we need
# the real data type to help us decide how to handle
# the retrieved data in an INSERT statement
foreach my $field (@.fields) {
if (! $conn->Sql("sp_help '$attrib{$field}'") )
{
while($conn->FetchRow()) {
my ($type) = $conn->Data("Storage_type");
$attrib{$field} = $type;
}
}
if ($attrib{$field} =~ /^(image|sql_variant)$/i) {
die "***Err: data type $attrib{$field} not
supported.\n";
}
push @.columns, $field if lc($attrib{$field})
ne 'timestamp';
}
}
else {
die "***Err: failed to run select * from $tbName
where 1=2.\n";
}
return (\@.columns, \%attrib);
} # getColumnProperties
########################
sub constructINSERT {
my($columnRef, $attribRef, $conn) = @._;
(scalar @.$columnRef && scalar %$attribRef) or
die "Err: \$columnRef or \$attribRef is empty.\n";
my $sql;
if (! $conn->Sql("select * from $tbName") ) {
# now get the data values for each row
while ($conn->FetchRow()) {
$sql .= "INSERT $tbName (" . join(',',
@.$columnRef) . ")\n";
my @.values = ();
my %data = $conn->DataHash();
# decide how to handle the VALUE clause of the
INSERT
foreach my $column (@.$columnRef) {
# the values of these data types can be used
as is
if ($attribRef->{$column}
=~ /int|smallint|bigint|tinyint|
bit|decimal|numeric|money|
smallmoney|float|real
/ix) {
if (defined $data{$column}) {
push @.values, $data{$column};
}
else {
push @.values, 'NULL';
}
}
# the values of these types must be quoted
with a pair of
# single quotation marks
elsif ($attribRef->{$column}
=~ /datetime|smalldatetime|
char|varchar|nchar|nvarchar|
text|ntext|uniqueidentifier
/ix) {
if (defined $data{$column}) {
$data{$column} =~ s/'/''/g;
push @.values, "'$data{$column}'";
}
else {
push @.values, 'NULL';
}
}
# the binary data must be converted to a HEX
string format
elsif ($attribRef->{$column}
=~ /binary|varbinary
/ix) {
if (defined $data{$column}) {
push @.values, '0x' . unpack("H*", $data
{$column});
}
else {
push @.values, 'NULL';
}
}
else {
print "***Assert: invalid code path. Skip
this row.\n";
next;
}
}
$sql .= "VALUES (" . join(',', @.values) . ")\n";
}
}
return $sql;
} # construtcINSERT
###################
sub printUsage {
print << '--Usage--';
Usage:
cmd>perl GeneratedataInserts.pl -S <SQL server or
instance>
-d <database name>
[ -t <table name> ]
--Usage--
exit;
} # printUsage
##############End script###############
quote:
>--Original Message--
>I have data in a table that I wish to transfer to a
remote system (both SQL
quote:
>2000).
>Is there an easy way to generate a series of insert
statements from my
quote:
>database so I can transfer this text file to my remote
system. If possible
quote:
>I like to be able to exclude data with a where statement.
>Regards
>Jeff
>
>--
>Outgoing mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.564 / Virus Database: 356 - Release Date:
19/01/2004
quote:|||If that's your sole purpose, you can get by more easily with
>
>.
>
1. creating a separate db
2. put the data in a table in that db
3. backup that db
4. do the reverse on the remove server.
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:Oxo8g173DHA.540@.tk2msftngp13.phx.gbl...
quote:
> I have data in a table that I wish to transfer to a remote system (both
SQL
quote:
> 2000).
> Is there an easy way to generate a series of insert statements from my
> database so I can transfer this text file to my remote system. If
possible
quote:
> I like to be able to exclude data with a where statement.
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.564 / Virus Database: 356 - Release Date: 19/01/2004
>
Generate insert statement for table
I have data in a table that I wish to transfer to a remote system (both SQL
2000).
Is there an easy way to generate a series of insert statements from my
database so I can transfer this text file to my remote system. If possible
I like to be able to exclude data with a where statement.
Regards
Jeff
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.564 / Virus Database: 356 - Release Date: 19/01/2004http://vyaskn.tripod.com/code.htm#inserts
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:Oxo8g173DHA.540@.tk2msftngp13.phx.gbl...
> I have data in a table that I wish to transfer to a remote system (both
SQL
> 2000).
> Is there an easy way to generate a series of insert statements from my
> database so I can transfer this text file to my remote system. If
possible
> I like to be able to exclude data with a where statement.
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.564 / Virus Database: 356 - Release Date: 19/01/2004
>|||Here's a Perl script for generating SQL INSERT statements.
The script deals with all data types, except image and
sql_variant, but including the user defined data types.
#############Begin script###########
# Created by: Linchi Shea
use strict;
use Data::Dumper;
use Win32::ODBC;
use Getopt::Std;
my %opts;
getopts('S:d:t:', \%opts);
my ($server, $dbName, $tbName) = ($opts{S}, $opts{d}, $opts
{t});
(defined $server && defined $dbName && defined $tbName) or
printUsage();
Main: {
my $connStr = "Driver={SQL Server};Server=$server;" .
"Database=$dbName;Trusted_Connection=yes";
my $conn = new Win32::ODBC($connStr) or
die "***Err: " . Win32::ODBC::Error();
my ($columnRef, $attribRef) = getColumnProperties
($tbName, $conn);
my $sql = constructINSERT($columnRef, $attribRef,
$conn);
print $sql;
$conn->Close();
} # Main
############################
sub getColumnProperties {
my($tbName, $conn) = @._;
my @.columns;
my %attrib;
if (! $conn->Sql("select * from $tbName where 1=2") ) {
1 while $conn->FetchRow();
# first get the data type for each column
my @.fields = $conn->FieldNames();
%attrib = $conn->ColAttributes($conn-
>SQL_COLUMN_TYPE_NAME, @.fields);
# in case the data type is user defined, we need
# the real data type to help us decide how to handle
# the retrieved data in an INSERT statement
foreach my $field (@.fields) {
if (! $conn->Sql("sp_help \'$attrib{$field}\'") )
{
while($conn->FetchRow()) {
my ($type) = $conn->Data("Storage_type");
$attrib{$field} = $type;
}
}
if ($attrib{$field} =~ /^(image|sql_variant)$/i) {
die "***Err: data type $attrib{$field} not
supported.\n";
}
push @.columns, $field if lc($attrib{$field})
ne 'timestamp';
}
}
else {
die "***Err: failed to run select * from $tbName
where 1=2.\n";
}
return (\@.columns, \%attrib);
} # getColumnProperties
########################
sub constructINSERT {
my($columnRef, $attribRef, $conn) = @._;
(scalar @.$columnRef && scalar %$attribRef) or
die "Err: \$columnRef or \$attribRef is empty.\n";
my $sql;
if (! $conn->Sql("select * from $tbName") ) {
# now get the data values for each row
while ($conn->FetchRow()) {
$sql .= "INSERT $tbName (" . join(',',
@.$columnRef) . ")\n";
my @.values = ();
my %data = $conn->DataHash();
# decide how to handle the VALUE clause of the
INSERT
foreach my $column (@.$columnRef) {
# the values of these data types can be used
as is
if ($attribRef->{$column}
=~ /int|smallint|bigint|tinyint|
bit|decimal|numeric|money|
smallmoney|float|real
/ix) {
if (defined $data{$column}) {
push @.values, $data{$column};
}
else {
push @.values, 'NULL';
}
}
# the values of these types must be quoted
with a pair of
# single quotation marks
elsif ($attribRef->{$column}
=~ /datetime|smalldatetime|
char|varchar|nchar|nvarchar|
text|ntext|uniqueidentifier
/ix) {
if (defined $data{$column}) {
$data{$column} =~ s/\'/\'\'/g;
push @.values, "\'$data{$column}\'";
}
else {
push @.values, 'NULL';
}
}
# the binary data must be converted to a HEX
string format
elsif ($attribRef->{$column}
=~ /binary|varbinary
/ix) {
if (defined $data{$column}) {
push @.values, '0x' . unpack("H*", $data
{$column});
}
else {
push @.values, 'NULL';
}
}
else {
print "***Assert: invalid code path. Skip
this row.\n";
next;
}
}
$sql .= "VALUES (" . join(',', @.values) . ")\n";
}
}
return $sql;
} # construtcINSERT
###################
sub printUsage {
print << '--Usage--';
Usage:
cmd>perl GeneratedataInserts.pl -S <SQL server or
instance>
-d <database name>
[ -t <table name> ]
--Usage--
exit;
} # printUsage
##############End script###############
>--Original Message--
>I have data in a table that I wish to transfer to a
remote system (both SQL
>2000).
>Is there an easy way to generate a series of insert
statements from my
>database so I can transfer this text file to my remote
system. If possible
>I like to be able to exclude data with a where statement.
>Regards
>Jeff
>
>--
>Outgoing mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.564 / Virus Database: 356 - Release Date:
19/01/2004
>
>.
>|||If that's your sole purpose, you can get by more easily with
1. creating a separate db
2. put the data in a table in that db
3. backup that db
4. do the reverse on the remove server.
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:Oxo8g173DHA.540@.tk2msftngp13.phx.gbl...
> I have data in a table that I wish to transfer to a remote system (both
SQL
> 2000).
> Is there an easy way to generate a series of insert statements from my
> database so I can transfer this text file to my remote system. If
possible
> I like to be able to exclude data with a where statement.
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.564 / Virus Database: 356 - Release Date: 19/01/2004
>
2000).
Is there an easy way to generate a series of insert statements from my
database so I can transfer this text file to my remote system. If possible
I like to be able to exclude data with a where statement.
Regards
Jeff
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.564 / Virus Database: 356 - Release Date: 19/01/2004http://vyaskn.tripod.com/code.htm#inserts
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:Oxo8g173DHA.540@.tk2msftngp13.phx.gbl...
> I have data in a table that I wish to transfer to a remote system (both
SQL
> 2000).
> Is there an easy way to generate a series of insert statements from my
> database so I can transfer this text file to my remote system. If
possible
> I like to be able to exclude data with a where statement.
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.564 / Virus Database: 356 - Release Date: 19/01/2004
>|||Here's a Perl script for generating SQL INSERT statements.
The script deals with all data types, except image and
sql_variant, but including the user defined data types.
#############Begin script###########
# Created by: Linchi Shea
use strict;
use Data::Dumper;
use Win32::ODBC;
use Getopt::Std;
my %opts;
getopts('S:d:t:', \%opts);
my ($server, $dbName, $tbName) = ($opts{S}, $opts{d}, $opts
{t});
(defined $server && defined $dbName && defined $tbName) or
printUsage();
Main: {
my $connStr = "Driver={SQL Server};Server=$server;" .
"Database=$dbName;Trusted_Connection=yes";
my $conn = new Win32::ODBC($connStr) or
die "***Err: " . Win32::ODBC::Error();
my ($columnRef, $attribRef) = getColumnProperties
($tbName, $conn);
my $sql = constructINSERT($columnRef, $attribRef,
$conn);
print $sql;
$conn->Close();
} # Main
############################
sub getColumnProperties {
my($tbName, $conn) = @._;
my @.columns;
my %attrib;
if (! $conn->Sql("select * from $tbName where 1=2") ) {
1 while $conn->FetchRow();
# first get the data type for each column
my @.fields = $conn->FieldNames();
%attrib = $conn->ColAttributes($conn-
>SQL_COLUMN_TYPE_NAME, @.fields);
# in case the data type is user defined, we need
# the real data type to help us decide how to handle
# the retrieved data in an INSERT statement
foreach my $field (@.fields) {
if (! $conn->Sql("sp_help \'$attrib{$field}\'") )
{
while($conn->FetchRow()) {
my ($type) = $conn->Data("Storage_type");
$attrib{$field} = $type;
}
}
if ($attrib{$field} =~ /^(image|sql_variant)$/i) {
die "***Err: data type $attrib{$field} not
supported.\n";
}
push @.columns, $field if lc($attrib{$field})
ne 'timestamp';
}
}
else {
die "***Err: failed to run select * from $tbName
where 1=2.\n";
}
return (\@.columns, \%attrib);
} # getColumnProperties
########################
sub constructINSERT {
my($columnRef, $attribRef, $conn) = @._;
(scalar @.$columnRef && scalar %$attribRef) or
die "Err: \$columnRef or \$attribRef is empty.\n";
my $sql;
if (! $conn->Sql("select * from $tbName") ) {
# now get the data values for each row
while ($conn->FetchRow()) {
$sql .= "INSERT $tbName (" . join(',',
@.$columnRef) . ")\n";
my @.values = ();
my %data = $conn->DataHash();
# decide how to handle the VALUE clause of the
INSERT
foreach my $column (@.$columnRef) {
# the values of these data types can be used
as is
if ($attribRef->{$column}
=~ /int|smallint|bigint|tinyint|
bit|decimal|numeric|money|
smallmoney|float|real
/ix) {
if (defined $data{$column}) {
push @.values, $data{$column};
}
else {
push @.values, 'NULL';
}
}
# the values of these types must be quoted
with a pair of
# single quotation marks
elsif ($attribRef->{$column}
=~ /datetime|smalldatetime|
char|varchar|nchar|nvarchar|
text|ntext|uniqueidentifier
/ix) {
if (defined $data{$column}) {
$data{$column} =~ s/\'/\'\'/g;
push @.values, "\'$data{$column}\'";
}
else {
push @.values, 'NULL';
}
}
# the binary data must be converted to a HEX
string format
elsif ($attribRef->{$column}
=~ /binary|varbinary
/ix) {
if (defined $data{$column}) {
push @.values, '0x' . unpack("H*", $data
{$column});
}
else {
push @.values, 'NULL';
}
}
else {
print "***Assert: invalid code path. Skip
this row.\n";
next;
}
}
$sql .= "VALUES (" . join(',', @.values) . ")\n";
}
}
return $sql;
} # construtcINSERT
###################
sub printUsage {
print << '--Usage--';
Usage:
cmd>perl GeneratedataInserts.pl -S <SQL server or
instance>
-d <database name>
[ -t <table name> ]
--Usage--
exit;
} # printUsage
##############End script###############
>--Original Message--
>I have data in a table that I wish to transfer to a
remote system (both SQL
>2000).
>Is there an easy way to generate a series of insert
statements from my
>database so I can transfer this text file to my remote
system. If possible
>I like to be able to exclude data with a where statement.
>Regards
>Jeff
>
>--
>Outgoing mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.564 / Virus Database: 356 - Release Date:
19/01/2004
>
>.
>|||If that's your sole purpose, you can get by more easily with
1. creating a separate db
2. put the data in a table in that db
3. backup that db
4. do the reverse on the remove server.
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:Oxo8g173DHA.540@.tk2msftngp13.phx.gbl...
> I have data in a table that I wish to transfer to a remote system (both
SQL
> 2000).
> Is there an easy way to generate a series of insert statements from my
> database so I can transfer this text file to my remote system. If
possible
> I like to be able to exclude data with a where statement.
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.564 / Virus Database: 356 - Release Date: 19/01/2004
>
Generate Cross tab temp table
Hi,
I have a table like this:
Year OfficeID Revenue
--
2005 1 13
2005 2 14
2005 3 12
And about 100 0other rows.
What SQL statements can give me the following result:
Year Office1 Office2 Office3 ........
---
2005 13 14 12 ........
TIAhttp://www.aspfaq.com/2462
"Ed Chiu" <EdChiu@.discussions.microsoft.com> wrote in message
news:B1F06E5B-A9C8-4977-B891-862F8979E3A1@.microsoft.com...
> Hi,
> I have a table like this:
> Year OfficeID Revenue
> --
> 2005 1 13
> 2005 2 14
> 2005 3 12
> And about 100 0other rows.
> What SQL statements can give me the following result:
> Year Office1 Office2 Office3 ........
> ---
> 2005 13 14 12 ........
> TIA|||Try,
select
Year,
sum(case when OfficeID = 1 then Revenue else 0 end) as OfficeID1,
sum(case when OfficeID = 2 then Revenue else 0 end) as OfficeID2,
sum(case when OfficeID = 3 then Revenue else 0 end) as OfficeID3
from
t1
group by
Year
go
HOW TO: Rotate a Table in SQL Server
http://support.microsoft.com/defaul...574&Product=sql
AMB
"Ed Chiu" wrote:
> Hi,
> I have a table like this:
> Year OfficeID Revenue
> --
> 2005 1 13
> 2005 2 14
> 2005 3 12
> And about 100 0other rows.
> What SQL statements can give me the following result:
> Year Office1 Office2 Office3 ........
> ---
> 2005 13 14 12 ........
> TIA
I have a table like this:
Year OfficeID Revenue
--
2005 1 13
2005 2 14
2005 3 12
And about 100 0other rows.
What SQL statements can give me the following result:
Year Office1 Office2 Office3 ........
---
2005 13 14 12 ........
TIAhttp://www.aspfaq.com/2462
"Ed Chiu" <EdChiu@.discussions.microsoft.com> wrote in message
news:B1F06E5B-A9C8-4977-B891-862F8979E3A1@.microsoft.com...
> Hi,
> I have a table like this:
> Year OfficeID Revenue
> --
> 2005 1 13
> 2005 2 14
> 2005 3 12
> And about 100 0other rows.
> What SQL statements can give me the following result:
> Year Office1 Office2 Office3 ........
> ---
> 2005 13 14 12 ........
> TIA|||Try,
select
Year,
sum(case when OfficeID = 1 then Revenue else 0 end) as OfficeID1,
sum(case when OfficeID = 2 then Revenue else 0 end) as OfficeID2,
sum(case when OfficeID = 3 then Revenue else 0 end) as OfficeID3
from
t1
group by
Year
go
HOW TO: Rotate a Table in SQL Server
http://support.microsoft.com/defaul...574&Product=sql
AMB
"Ed Chiu" wrote:
> Hi,
> I have a table like this:
> Year OfficeID Revenue
> --
> 2005 1 13
> 2005 2 14
> 2005 3 12
> And about 100 0other rows.
> What SQL statements can give me the following result:
> Year Office1 Office2 Office3 ........
> ---
> 2005 13 14 12 ........
> TIA
Generate ALTER scripts
Hi. In SSMS (9.00.3042) I'm trying to generate a single file containing
ALTER statements for all the SPs, views and UDFs in a database. In the
Generate Scripts wizard, I've only been able to generate create and drop
statements. Any tips are appreciated.
Thanks,
Ben
I found a suggestion on Connect for this:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289038
Unfortunately, Microsoft closed the suggestion last month without fixing it.
Their comments was ... "Alter scripting post multi select isnt supported and
object alter scripts in the UI isnt going to be easy to manage." I'm not
sure what would be so difficult.
Ben
|||Ben
What if you script out all objects and just REPLACE 'create' with 'alter'
( i.e in notepad or something)?
"Ben Amada" <ben.nojunkplease.amada@.gmail.com> wrote in message
news:46B2F2F6-E9E1-43A7-94D0-29A8A4D028DC@.microsoft.com...
>I found a suggestion on Connect for this:
> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289038
> Unfortunately, Microsoft closed the suggestion last month without fixing
> it. Their comments was ... "Alter scripting post multi select isnt
> supported and object alter scripts in the UI isnt going to be easy to
> manage." I'm not sure what would be so difficult.
> Ben
|||"Uri Dimant" <urid@.iscar.co.il> wrote: in message
news:ONtFmVPjIHA.748@.TK2MSFTNGP04.phx.gbl...
> Ben
> What if you script out all objects and just REPLACE 'create' with 'alter'
> ( i.e in notepad or something)?
Hi Uri. I looked at doing that once, but noticed the word "create" was also
in the name of some SPs (e.g. CreateRecord) and in some comments within some
of these objects. I also looked at replacing "CREATE PROC" with "ALTER
PROC", but then I saw how a good number of the procedures have multiple
spaces between CREATE and PROC !! This might be possible to do, however, if
I came up with a regular expression and did a search/replace in Notepad++
(or equivalent editor). Whatever I do, I just want to make sure it's not
too difficult to be able to repeat as I may need to regenerate the ALTER
statements again.
Thank you,
Ben
|||"Ben Amada" <ben.nojunkplease.amada@.gmail.com> wrote:
> This might be possible to do, however, if I came up with a regular
> expression and did a search/replace in Notepad++ (or equivalent editor).
These regular expressions work in Notepad++:
^\s*(create)\s+(proc)
^\s*(create)\s+(view)
^\s*(create)\s+(function)
Leading spaces and tabs are ignored and multiple spaces and/or tabs can be
between the CREATE word and the following word (proc, view or function).
The only thing it doesn't take into account would be if CREATE and PROC were
on two different lines. But, I don't think I have any of those.
|||"Ben Amada" <ben.nojunkplease.amada@.gmail.com> wrote in message
news:46B2F2F6-E9E1-43A7-94D0-29A8A4D028DC@.microsoft.com...
>I found a suggestion on Connect for this:
> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289038
> Unfortunately, Microsoft closed the suggestion last month without fixing
> it. Their comments was ... "Alter scripting post multi select isnt
> supported and object alter scripts in the UI isnt going to be easy to
> manage." I'm not sure what would be so difficult.
There is hope yet (but not for SQL Server 2008). Here is a suggestion I
submitted almost a year ago, and it is still active:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=273938
|||You can always write a little script in any language that supports regular
expressions to change CRAETE PROC (or a combo of all the allowed syntactic
formats) to ALTER PROC. This is a much better approach than demanding a GUI
for every little possible syntactic format.
Linchi
"Ben Amada" wrote:
> "Ben Amada" <ben.nojunkplease.amada@.gmail.com> wrote:
>
> These regular expressions work in Notepad++:
> ^\s*(create)\s+(proc)
> ^\s*(create)\s+(view)
> ^\s*(create)\s+(function)
> Leading spaces and tabs are ignored and multiple spaces and/or tabs can be
> between the CREATE word and the following word (proc, view or function).
> The only thing it doesn't take into account would be if CREATE and PROC were
> on two different lines. But, I don't think I have any of those.
>
|||"Linchi Shea" wrote:
> You can always write a little script in any language that supports regular
> expressions to change CRAETE PROC (or a combo of all the allowed syntactic
> formats) to ALTER PROC. This is a much better approach than demanding a
> GUI
> for every little possible syntactic format.
Hi. In the long run, you're probably right ... a single script or program
to do all the conversions in one shot would be best. The regular
expressions I posted a little earlier should account for all the variations
I have to deal with. I just have to do a search & replace 3 times (once for
each object type). Heck, I could have put together a small .NET app to
replace Create with Alter in less time than it took me to come up with the
regular expression :-)
|||Hello Ben,
> "Linchi Shea" wrote:
> Hi. In the long run, you're probably right ... a single script or
> program to do all the conversions in one shot would be best. The
> regular expressions I posted a little earlier should account for all
> the variations I have to deal with. I just have to do a search &
> replace 3 times (once for each object type). Heck, I could have put
> together a small .NET app to replace Create with Alter in less time
> than it took me to come up with the regular expression :-)
You can even use the following syntax:
^\s*create\s+(proc|table|trigger|...)
and replace it with:
ALTER $1
which should allow you to search and replace in one pass
Jesse Houwing
jesse.houwing at sogeti.nl
|||"Jesse Houwing" wrote:
> You can even use the following syntax:
> ^\s*create\s+(proc|table|trigger|...)
> and replace it with:
> ALTER $1
> which should allow you to search and replace in one pass
That's really slick Jesse ... thanks!
ALTER statements for all the SPs, views and UDFs in a database. In the
Generate Scripts wizard, I've only been able to generate create and drop
statements. Any tips are appreciated.
Thanks,
Ben
I found a suggestion on Connect for this:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289038
Unfortunately, Microsoft closed the suggestion last month without fixing it.
Their comments was ... "Alter scripting post multi select isnt supported and
object alter scripts in the UI isnt going to be easy to manage." I'm not
sure what would be so difficult.
Ben
|||Ben
What if you script out all objects and just REPLACE 'create' with 'alter'
( i.e in notepad or something)?
"Ben Amada" <ben.nojunkplease.amada@.gmail.com> wrote in message
news:46B2F2F6-E9E1-43A7-94D0-29A8A4D028DC@.microsoft.com...
>I found a suggestion on Connect for this:
> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289038
> Unfortunately, Microsoft closed the suggestion last month without fixing
> it. Their comments was ... "Alter scripting post multi select isnt
> supported and object alter scripts in the UI isnt going to be easy to
> manage." I'm not sure what would be so difficult.
> Ben
|||"Uri Dimant" <urid@.iscar.co.il> wrote: in message
news:ONtFmVPjIHA.748@.TK2MSFTNGP04.phx.gbl...
> Ben
> What if you script out all objects and just REPLACE 'create' with 'alter'
> ( i.e in notepad or something)?
Hi Uri. I looked at doing that once, but noticed the word "create" was also
in the name of some SPs (e.g. CreateRecord) and in some comments within some
of these objects. I also looked at replacing "CREATE PROC" with "ALTER
PROC", but then I saw how a good number of the procedures have multiple
spaces between CREATE and PROC !! This might be possible to do, however, if
I came up with a regular expression and did a search/replace in Notepad++
(or equivalent editor). Whatever I do, I just want to make sure it's not
too difficult to be able to repeat as I may need to regenerate the ALTER
statements again.
Thank you,
Ben
|||"Ben Amada" <ben.nojunkplease.amada@.gmail.com> wrote:
> This might be possible to do, however, if I came up with a regular
> expression and did a search/replace in Notepad++ (or equivalent editor).
These regular expressions work in Notepad++:
^\s*(create)\s+(proc)
^\s*(create)\s+(view)
^\s*(create)\s+(function)
Leading spaces and tabs are ignored and multiple spaces and/or tabs can be
between the CREATE word and the following word (proc, view or function).
The only thing it doesn't take into account would be if CREATE and PROC were
on two different lines. But, I don't think I have any of those.
|||"Ben Amada" <ben.nojunkplease.amada@.gmail.com> wrote in message
news:46B2F2F6-E9E1-43A7-94D0-29A8A4D028DC@.microsoft.com...
>I found a suggestion on Connect for this:
> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289038
> Unfortunately, Microsoft closed the suggestion last month without fixing
> it. Their comments was ... "Alter scripting post multi select isnt
> supported and object alter scripts in the UI isnt going to be easy to
> manage." I'm not sure what would be so difficult.
There is hope yet (but not for SQL Server 2008). Here is a suggestion I
submitted almost a year ago, and it is still active:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=273938
|||You can always write a little script in any language that supports regular
expressions to change CRAETE PROC (or a combo of all the allowed syntactic
formats) to ALTER PROC. This is a much better approach than demanding a GUI
for every little possible syntactic format.
Linchi
"Ben Amada" wrote:
> "Ben Amada" <ben.nojunkplease.amada@.gmail.com> wrote:
>
> These regular expressions work in Notepad++:
> ^\s*(create)\s+(proc)
> ^\s*(create)\s+(view)
> ^\s*(create)\s+(function)
> Leading spaces and tabs are ignored and multiple spaces and/or tabs can be
> between the CREATE word and the following word (proc, view or function).
> The only thing it doesn't take into account would be if CREATE and PROC were
> on two different lines. But, I don't think I have any of those.
>
|||"Linchi Shea" wrote:
> You can always write a little script in any language that supports regular
> expressions to change CRAETE PROC (or a combo of all the allowed syntactic
> formats) to ALTER PROC. This is a much better approach than demanding a
> GUI
> for every little possible syntactic format.
Hi. In the long run, you're probably right ... a single script or program
to do all the conversions in one shot would be best. The regular
expressions I posted a little earlier should account for all the variations
I have to deal with. I just have to do a search & replace 3 times (once for
each object type). Heck, I could have put together a small .NET app to
replace Create with Alter in less time than it took me to come up with the
regular expression :-)
|||Hello Ben,
> "Linchi Shea" wrote:
> Hi. In the long run, you're probably right ... a single script or
> program to do all the conversions in one shot would be best. The
> regular expressions I posted a little earlier should account for all
> the variations I have to deal with. I just have to do a search &
> replace 3 times (once for each object type). Heck, I could have put
> together a small .NET app to replace Create with Alter in less time
> than it took me to come up with the regular expression :-)
You can even use the following syntax:
^\s*create\s+(proc|table|trigger|...)
and replace it with:
ALTER $1
which should allow you to search and replace in one pass
Jesse Houwing
jesse.houwing at sogeti.nl
|||"Jesse Houwing" wrote:
> You can even use the following syntax:
> ^\s*create\s+(proc|table|trigger|...)
> and replace it with:
> ALTER $1
> which should allow you to search and replace in one pass
That's really slick Jesse ... thanks!
Generate ALTER scripts
Hi. In SSMS (9.00.3042) I'm trying to generate a single file containing
ALTER statements for all the SPs, views and UDFs in a database. In the
Generate Scripts wizard, I've only been able to generate create and drop
statements. Any tips are appreciated.
Thanks,
BenI found a suggestion on Connect for this:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289038
Unfortunately, Microsoft closed the suggestion last month without fixing it.
Their comments was ... "Alter scripting post multi select isnt supported and
object alter scripts in the UI isnt going to be easy to manage." I'm not
sure what would be so difficult.
Ben|||Ben
What if you script out all objects and just REPLACE 'create' with 'alter'
( i.e in notepad or something)?
"Ben Amada" <ben.nojunkplease.amada@.gmail.com> wrote in message
news:46B2F2F6-E9E1-43A7-94D0-29A8A4D028DC@.microsoft.com...
>I found a suggestion on Connect for this:
> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289038
> Unfortunately, Microsoft closed the suggestion last month without fixing
> it. Their comments was ... "Alter scripting post multi select isnt
> supported and object alter scripts in the UI isnt going to be easy to
> manage." I'm not sure what would be so difficult.
> Ben|||"Uri Dimant" <urid@.iscar.co.il> wrote: in message
news:ONtFmVPjIHA.748@.TK2MSFTNGP04.phx.gbl...
> Ben
> What if you script out all objects and just REPLACE 'create' with 'alter'
> ( i.e in notepad or something)?
Hi Uri. I looked at doing that once, but noticed the word "create" was also
in the name of some SPs (e.g. CreateRecord) and in some comments within some
of these objects. I also looked at replacing "CREATE PROC" with "ALTER
PROC", but then I saw how a good number of the procedures have multiple
spaces between CREATE and PROC !! This might be possible to do, however, if
I came up with a regular expression and did a search/replace in Notepad++
(or equivalent editor). Whatever I do, I just want to make sure it's not
too difficult to be able to repeat as I may need to regenerate the ALTER
statements again.
Thank you,
Ben|||"Ben Amada" <ben.nojunkplease.amada@.gmail.com> wrote:
> This might be possible to do, however, if I came up with a regular
> expression and did a search/replace in Notepad++ (or equivalent editor).
These regular expressions work in Notepad++:
^\s*(create)\s+(proc)
^\s*(create)\s+(view)
^\s*(create)\s+(function)
Leading spaces and tabs are ignored and multiple spaces and/or tabs can be
between the CREATE word and the following word (proc, view or function).
The only thing it doesn't take into account would be if CREATE and PROC were
on two different lines. But, I don't think I have any of those.|||"Ben Amada" <ben.nojunkplease.amada@.gmail.com> wrote in message
news:46B2F2F6-E9E1-43A7-94D0-29A8A4D028DC@.microsoft.com...
>I found a suggestion on Connect for this:
> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289038
> Unfortunately, Microsoft closed the suggestion last month without fixing
> it. Their comments was ... "Alter scripting post multi select isnt
> supported and object alter scripts in the UI isnt going to be easy to
> manage." I'm not sure what would be so difficult.
There is hope yet (but not for SQL Server 2008). Here is a suggestion I
submitted almost a year ago, and it is still active:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=273938|||You can always write a little script in any language that supports regular
expressions to change CRAETE PROC (or a combo of all the allowed syntactic
formats) to ALTER PROC. This is a much better approach than demanding a GUI
for every little possible syntactic format.
Linchi
"Ben Amada" wrote:
> "Ben Amada" <ben.nojunkplease.amada@.gmail.com> wrote:
> > This might be possible to do, however, if I came up with a regular
> > expression and did a search/replace in Notepad++ (or equivalent editor).
> These regular expressions work in Notepad++:
> ^\s*(create)\s+(proc)
> ^\s*(create)\s+(view)
> ^\s*(create)\s+(function)
> Leading spaces and tabs are ignored and multiple spaces and/or tabs can be
> between the CREATE word and the following word (proc, view or function).
> The only thing it doesn't take into account would be if CREATE and PROC were
> on two different lines. But, I don't think I have any of those.
>|||"Linchi Shea" wrote:
> You can always write a little script in any language that supports regular
> expressions to change CRAETE PROC (or a combo of all the allowed syntactic
> formats) to ALTER PROC. This is a much better approach than demanding a
> GUI
> for every little possible syntactic format.
Hi. In the long run, you're probably right ... a single script or program
to do all the conversions in one shot would be best. The regular
expressions I posted a little earlier should account for all the variations
I have to deal with. I just have to do a search & replace 3 times (once for
each object type). Heck, I could have put together a small .NET app to
replace Create with Alter in less time than it took me to come up with the
regular expression :-)
ALTER statements for all the SPs, views and UDFs in a database. In the
Generate Scripts wizard, I've only been able to generate create and drop
statements. Any tips are appreciated.
Thanks,
BenI found a suggestion on Connect for this:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289038
Unfortunately, Microsoft closed the suggestion last month without fixing it.
Their comments was ... "Alter scripting post multi select isnt supported and
object alter scripts in the UI isnt going to be easy to manage." I'm not
sure what would be so difficult.
Ben|||Ben
What if you script out all objects and just REPLACE 'create' with 'alter'
( i.e in notepad or something)?
"Ben Amada" <ben.nojunkplease.amada@.gmail.com> wrote in message
news:46B2F2F6-E9E1-43A7-94D0-29A8A4D028DC@.microsoft.com...
>I found a suggestion on Connect for this:
> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289038
> Unfortunately, Microsoft closed the suggestion last month without fixing
> it. Their comments was ... "Alter scripting post multi select isnt
> supported and object alter scripts in the UI isnt going to be easy to
> manage." I'm not sure what would be so difficult.
> Ben|||"Uri Dimant" <urid@.iscar.co.il> wrote: in message
news:ONtFmVPjIHA.748@.TK2MSFTNGP04.phx.gbl...
> Ben
> What if you script out all objects and just REPLACE 'create' with 'alter'
> ( i.e in notepad or something)?
Hi Uri. I looked at doing that once, but noticed the word "create" was also
in the name of some SPs (e.g. CreateRecord) and in some comments within some
of these objects. I also looked at replacing "CREATE PROC" with "ALTER
PROC", but then I saw how a good number of the procedures have multiple
spaces between CREATE and PROC !! This might be possible to do, however, if
I came up with a regular expression and did a search/replace in Notepad++
(or equivalent editor). Whatever I do, I just want to make sure it's not
too difficult to be able to repeat as I may need to regenerate the ALTER
statements again.
Thank you,
Ben|||"Ben Amada" <ben.nojunkplease.amada@.gmail.com> wrote:
> This might be possible to do, however, if I came up with a regular
> expression and did a search/replace in Notepad++ (or equivalent editor).
These regular expressions work in Notepad++:
^\s*(create)\s+(proc)
^\s*(create)\s+(view)
^\s*(create)\s+(function)
Leading spaces and tabs are ignored and multiple spaces and/or tabs can be
between the CREATE word and the following word (proc, view or function).
The only thing it doesn't take into account would be if CREATE and PROC were
on two different lines. But, I don't think I have any of those.|||"Ben Amada" <ben.nojunkplease.amada@.gmail.com> wrote in message
news:46B2F2F6-E9E1-43A7-94D0-29A8A4D028DC@.microsoft.com...
>I found a suggestion on Connect for this:
> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289038
> Unfortunately, Microsoft closed the suggestion last month without fixing
> it. Their comments was ... "Alter scripting post multi select isnt
> supported and object alter scripts in the UI isnt going to be easy to
> manage." I'm not sure what would be so difficult.
There is hope yet (but not for SQL Server 2008). Here is a suggestion I
submitted almost a year ago, and it is still active:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=273938|||You can always write a little script in any language that supports regular
expressions to change CRAETE PROC (or a combo of all the allowed syntactic
formats) to ALTER PROC. This is a much better approach than demanding a GUI
for every little possible syntactic format.
Linchi
"Ben Amada" wrote:
> "Ben Amada" <ben.nojunkplease.amada@.gmail.com> wrote:
> > This might be possible to do, however, if I came up with a regular
> > expression and did a search/replace in Notepad++ (or equivalent editor).
> These regular expressions work in Notepad++:
> ^\s*(create)\s+(proc)
> ^\s*(create)\s+(view)
> ^\s*(create)\s+(function)
> Leading spaces and tabs are ignored and multiple spaces and/or tabs can be
> between the CREATE word and the following word (proc, view or function).
> The only thing it doesn't take into account would be if CREATE and PROC were
> on two different lines. But, I don't think I have any of those.
>|||"Linchi Shea" wrote:
> You can always write a little script in any language that supports regular
> expressions to change CRAETE PROC (or a combo of all the allowed syntactic
> formats) to ALTER PROC. This is a much better approach than demanding a
> GUI
> for every little possible syntactic format.
Hi. In the long run, you're probably right ... a single script or program
to do all the conversions in one shot would be best. The regular
expressions I posted a little earlier should account for all the variations
I have to deal with. I just have to do a search & replace 3 times (once for
each object type). Heck, I could have put together a small .NET app to
replace Create with Alter in less time than it took me to come up with the
regular expression :-)
Subscribe to:
Posts (Atom)