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?"

kmoskal@newsgroups.nospam (zork@newsgroups.nospam) writes:

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.

Erland Sommarskog (esquel@sommarskog.se) writes:

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

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/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@newsgroups.nospam (zork@newsgroups.nospam) writes: