 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / General / SQL Server Tools / August 2008Can 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?"  SignatureKurt > 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 [quoted text clipped - 3 lines] > 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. SignatureErland Sommarskog, SQL Server MVP, esquel@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/prodinfo/previousversions/books.mspx > In the mean while, here is a Perl one-liner for you: I forgot: download Perl from http://www.activestate.com. SignatureErland 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. SignatureKurt > > 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 [quoted text clipped - 27 lines] > Then again, if you have your code under version control, this should > not really be an issue. > How to create a script with indexes and Primary Keys only using SQL Server > 2000 Enterprise Manager [quoted text clipped - 16 lines] > 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. SignatureErland Sommarskog, SQL Server MVP, esquel@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/prodinfo/previousversions/books.mspx |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.