|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
How to generate drop and recreate index script in SQL Management SI am wondering it was so simple to create the script in SQL2000. How can we
generate the a script to drop and recreate the tables and associated indexes in SQL management studio 2005 Thx, -- Sr DBA Pier 1 Imports mab***@Pier1.com Well assuming you have SP2 loaded you can do this from SSMS by generating a
script similar to how it was done in 2000. Right click ont he db and choose Tasks - Generate Scripts. -- Show quoteAndrew J. Kelly SQL MVP "Moh" <mab***@Pier1.com> wrote in message news:66ED2530-32D6-46F9-9609-7CAE6D9A268E@microsoft.com... >I am wondering it was so simple to create the script in SQL2000. How can we > generate the a script to drop and recreate the tables and associated > indexes > in SQL management studio 2005 > > Thx, > -- > Sr DBA > Pier 1 Imports > mab***@Pier1.com you think so. did you tyry it out. I do have SP2 and it does not give option
to drop index unless I am missing which you know -- Show quoteSr DBA Pier 1 Imports mab***@Pier1.com "Andrew J. Kelly" wrote: > Well assuming you have SP2 loaded you can do this from SSMS by generating a > script similar to how it was done in 2000. Right click ont he db and choose > Tasks - Generate Scripts. > > -- > Andrew J. Kelly SQL MVP > > "Moh" <mab***@Pier1.com> wrote in message > news:66ED2530-32D6-46F9-9609-7CAE6D9A268E@microsoft.com... > >I am wondering it was so simple to create the script in SQL2000. How can we > > generate the a script to drop and recreate the tables and associated > > indexes > > in SQL management studio 2005 > > > > Thx, > > -- > > Sr DBA > > Pier 1 Imports > > mab***@Pier1.com > > > If you are just talking about the index by itself it may not. I would have
to try that. But you said tables and their indexes. When you drop the table the corresponding indexes get dropped as well. -- Show quoteAndrew J. Kelly SQL MVP "Moh" <mab***@Pier1.com> wrote in message news:4DF047CC-622D-46AA-ABD6-169D312F4C16@microsoft.com... > you think so. did you tyry it out. I do have SP2 and it does not give > option > to drop index unless I am missing which you know > -- > Sr DBA > Pier 1 Imports > mab***@Pier1.com > > > "Andrew J. Kelly" wrote: > >> Well assuming you have SP2 loaded you can do this from SSMS by generating >> a >> script similar to how it was done in 2000. Right click ont he db and >> choose >> Tasks - Generate Scripts. >> >> -- >> Andrew J. Kelly SQL MVP >> >> "Moh" <mab***@Pier1.com> wrote in message >> news:66ED2530-32D6-46F9-9609-7CAE6D9A268E@microsoft.com... >> >I am wondering it was so simple to create the script in SQL2000. How can >> >we >> > generate the a script to drop and recreate the tables and associated >> > indexes >> > in SQL management studio 2005 >> > >> > Thx, >> > -- >> > Sr DBA >> > Pier 1 Imports >> > mab***@Pier1.com >> >> >> Moh (mab***@Pier1.com) writes:
> you think so. did you tyry it out. I do have SP2 and it does not give In your original post, you talked about dropping tables and indexes, and> option to drop index unless I am missing which you know if you drop a table, off goes its indexes. And in SP2 it is possble to select that the script should include both create and drop. But if you only want to drop indexs to later create them, that option is not there. Then again, you can easily create such commands but running a query over sys.indexes. -- Erland Sommarskog, SQL Server MVP, esq***@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 Thx all for the help. I know that index can be created by using sys.indexes
tables. the question was why MS had this option in SQL2K in Enterprise manager and not in SQL2K5 in SSMS. -- Show quoteSr DBA Pier 1 Imports mab***@Pier1.com "Erland Sommarskog" wrote: > Moh (mab***@Pier1.com) writes: > > you think so. did you tyry it out. I do have SP2 and it does not give > > option to drop index unless I am missing which you know > > In your original post, you talked about dropping tables and indexes, and > if you drop a table, off goes its indexes. And in SP2 it is possble to > select that the script should include both create and drop. > > But if you only want to drop indexs to later create them, that option is > not there. Then again, you can easily create such commands but running > a query over sys.indexes. > > > -- > Erland Sommarskog, SQL Server MVP, esq***@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 > Moh (mab***@Pier1.com) writes:
> Thx all for the help. I know that index can be created by using For SQL 2005 MS wrote completely new tools, and it appears that they did> sys.indexes tables. the question was why MS had this option in SQL2K in > Enterprise manager and not in SQL2K5 in SSMS. not look to closely on what the old had to offer. There are plenty of features from the SQL 2000 tools which are missing from the 2005 toolset, and you are not the first user to be angered over this. In the current situation, I cannot do much more than make suggestions that work within the current framework. If you feel strongly about this, I urge to file a suggestion for improvement on http://connect.microsoft.com/SQLServer/. -- Erland Sommarskog, SQL Server MVP, esq***@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
Show quote
On Mar 30, 12:01 pm, "Andrew J. Kelly" <sqlmvpnooos***@shadhawk.com> You might also want to check this out: http://www.elsasoft.org/tools.htmwrote: > Well assuming you have SP2 loaded you can do this from SSMS by generating ascriptsimilar to how it was done in 2000. Right click ont he db and choose > Tasks -GenerateScripts. > > -- > Andrew J. KellySQLMVP > > "Moh" <mab***@Pier1.com> wrote in message > > news:66ED2530-32D6-46F9-9609-7CAE6D9A268E@microsoft.com... > > >I am wondering it was so simple to create thescriptin SQL2000. How can we > >generatethe ascriptto drop and recreate the tables and associated > > indexes > > inSQLmanagement studio 2005 > > > Thx, > > -- > > Sr DBA > > Pier 1 Imports > > mab***@Pier1.com it's a console app (open source) that will script out the entire database (2000 or 2005) using SMO. Creates a separate file for each object.  |
|||||||||||||||||||||||
Other interesting topics