Can I create a Scipt with only create index command?

I'm using SSMS with SQL 2005 SP2. I've been trying to create a script using
the "Generate Script" wizard that only has Drop Index and Create Index
commands. I don’t want the create table command. The database has about 500
tables and it will take me hours to generate a FULL script and manually edit
out all the create table commands. I swear this was an option in the old SQL
2000 Enterprise Manager. I'm I look for a "needle in a haystack?"


--
Kurt

kmoskal-at-newsgroups.nospam (zork-at-newsgroups.nospam) writes:
> I'm using SSMS with SQL 2005 SP2. I've been trying to create a script
> using the "Generate Script" wizard that only has Drop Index and Create
> Index commands. I don’t want the create table command. The database has
> about 500 tables and it will take me hours to generate a FULL script and
> manually edit out all the create table commands. I swear this was an
> option in the old SQL 2000 Enterprise Manager. I'm I look for a "needle
> in a haystack?"

I had a quick look in EM, and I could not find such option. But I have
not used scripting much in EM 2000.

And, no, you cannot do it in SSMS. But I agree that it could be useful.
http://connect.microsoft.com/SqlServer/Feedback/ is a good place to make
your word heard in the issue.

In the mean while, here is a Perl one-liner for you:

perl -ne "BEGIN {$/ = qq!\nGO\n!} print if /CREATE( UNIQUE)? (NON)?CLUSTERED
INDEX/" yourfile.sql

Where yourfile was created by the script wizard in SSMS.

Caveat 1: when you script the database make sure to select output to an ANSI
file.

Caveat 2: this does not include Primary keys and Unique constraints.

Caveat 3: my test file was created by SSMS 2008.


Then again, if you have your code under version control, this should
not really be an issue.


--
Erland Sommarskog, SQL Server MVP, esquel-at-sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Erland Sommarskog (esquel-at-sommarskog.se) writes:
> In the mean while, here is a Perl one-liner for you:

I forgot: download Perl from http://www.activestate.com.


--
Erland Sommarskog, SQL Server MVP, esquel-at-sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

How to create a script with indexes and Primary Keys only using SQL Server
2000 Enterprise Manager

In short, using SQL Server 2000 EM, from a database select the Generate
Script option.

When the wizard starts,

1. From the General Tab - select the script all object box
2. From the Formatting Tab remove all the checkboxes under the srcipting
options...
3. From the Options Tab, select Script Indexes and/or Script Primary keys...
4. Then either press OK or return to the General Tab and press preview.

The resulting output is a script of only create index commands and/or Alter
Table commands (used to create the PK constraint).

A very useful feature that is no longer available in SSMS 2005 or SSMS 2008.


--
Kurt


"Erland Sommarskog" wrote:

> kmoskal-at-newsgroups.nospam (zork-at-newsgroups.nospam) writes:
> > I'm using SSMS with SQL 2005 SP2. I've been trying to create a script
> > using the "Generate Script" wizard that only has Drop Index and Create
> > Index commands. I don’t want the create table command. The database has
> > about 500 tables and it will take me hours to generate a FULL script and
> > manually edit out all the create table commands. I swear this was an
> > option in the old SQL 2000 Enterprise Manager. I'm I look for a "needle
> > in a haystack?"
>
> I had a quick look in EM, and I could not find such option. But I have
> not used scripting much in EM 2000.
>
> And, no, you cannot do it in SSMS. But I agree that it could be useful.
> http://connect.microsoft.com/SqlServer/Feedback/ is a good place to make
> your word heard in the issue.
>
> In the mean while, here is a Perl one-liner for you:
>
> perl -ne "BEGIN {$/ = qq!\nGO\n!} print if /CREATE( UNIQUE)? (NON)?CLUSTERED
> INDEX/" yourfile.sql
>
> Where yourfile was created by the script wizard in SSMS.
>
> Caveat 1: when you script the database make sure to select output to an ANSI
> file.
>
> Caveat 2: this does not include Primary keys and Unique constraints.
>
> Caveat 3: my test file was created by SSMS 2008.
>
>
> Then again, if you have your code under version control, this should
> not really be an issue.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel-at-sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx
>
>

kmoskal-at-newsgroups.nospam (zork-at-newsgroups.nospam) writes:
> How to create a script with indexes and Primary Keys only using SQL Server
> 2000 Enterprise Manager
>
> In short, using SQL Server 2000 EM, from a database select the Generate
> Script option.
>
> When the wizard starts,
>
> 1. From the General Tab - select the script all object box
> 2. From the Formatting Tab remove all the checkboxes under the srcipting
> options...
> 3. From the Options Tab, select Script Indexes and/or Script Primary
> keys... 4. Then either press OK or return to the General Tab and press
> preview.
>
> The resulting output is a script of only create index commands and/or
> Alter Table commands (used to create the PK constraint).
>
> A very useful feature that is no longer available in SSMS 2005 or SSMS
> 2008.

Thanks. I would say that the way to do it was not very apparent. It's
almost as if it works by accident.

I tried the corresponding in SSMS, but it was smarter, it scripted
nothing at all.

I'm afraid that you will have to make your own hack; my Perl hack could
be a start, or investigate the third-party market. Red Gate's SQL
Compare is able to script to file, but I don't have any copy available
to see what you selections you can make.

--
Erland Sommarskog, SQL Server MVP, esquel-at-sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx