Asger Blond
ab-mi at post3.tele.dk
Sun Mar 30 17:15:52 CDT 2008
Same in SSMS. In Enterprise Manager and in SSMS you can choose to script indexes for all tables in a db - BUT: your script will include CREATE TABLE as well - AND: it will also include an IF EXISTS MyDearTable THEN DROP MyDearTable before the script to create named dear table, so be careful to extract only the wanted parts of the script... Asger -----Oprindelig meddelelse----- Fra: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Eric Barro Sendt: 30. marts 2008 23:46 Til: 'Discussion concerning MS SQL Server' Emne: Re: [dba-SQLServer] Drop restore indexes Yeah, see...using Enterprise Manager you can just right click and go to Tasks and then Generate scripts and you are presented with a dialog box that allows you what objects you want to script. -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Asger Blond Sent: Sunday, March 30, 2008 2:28 PM To: 'Discussion concerning MS SQL Server' Subject: Re: [dba-SQLServer] Drop restore indexes John, Two options: 1. If your indexes are non-clustered the easiest way is: a) ALTER INDEX indexname ON tablename DISABLE - b) Do your appends - c) ALTER INDEX indexname ON tablename REBUILD. 2. If your indexes are clustered the first option is a no go, because disabling a clustered index will make the table inaccessible. Instead you can use SSMS to generate scripts for create and drop index, as pointed out by Arthur. In SSMS use Object Explorer to expand your table, expand Indexes, right-click an index and choose Script Index As -> Create / Drop. Repeat this for all indexes and all tables wanted. Then a) Run drop indexes scripts - b) Do your appends - c) Run create indexes scripts. For both options you can create a sproc for the DISABLE or DROP and another sproc for the REBUILD or CREATE on all wanted indexes on all wanted tables a once. Asger -----Oprindelig meddelelse----- Fra: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af jwcolby Sendt: 30. marts 2008 19:20 Til: 'Discussion concerning MS SQL Server' Emne: [dba-SQLServer] Drop restore indexes I am looking for a generic method to drop and then restore the indexes on a table. IOW, I have a bunch of tables that I have to do appends to, tens of millions of records. These tables may have dozens of indexes. I have always set up my indexes using the wizard so I don't have scripts already set up to create the indexes. I need to have some tool that will build scripts to recreate the indexes fond on a table and then remoce the indexes. Once the records are appended, I need to then run the scripts to rebuild all the saved index scripts. Is there such a tool? John W. Colby Colby Consulting www.ColbyConsulting.com _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com