Programming, SQL Database

Drop and recreate all indexes in database

click here SQL Server Management Studio does not have an easy way via the wizard to complete these tasks at the same time. In this tip, we look at a creating a script to drop and create all SQL Server indexes for a SQL Server database. Some cases we found indexes, not in a source control system, dropped or corrupted, indexes need to be recreated for another copy of the same database for testing, development or training purposes or there is a need to recreate the indexes on a different FILEGROUP then refer below script in order.

so how to drop and recreate all indexes in the database?

http://www.transportbudapesta.ro/?kdls=option-volatility-%26-pricing-advanced-trading-strategies-and-techniques&7b3=7d Get the copy of existing indexes as create the script:

http://penizeamy.cz/friopre/7166 SELECT ' CREATE ' + CASE WHEN I.is_unique = 1 THEN ' UNIQUE ' ELSE '' END + I.type_desc COLLATE DATABASE_DEFAULT +' INDEX ' + I.name + ' ON ' + Schema_name(T.Schema_id)+'.'+T.name + ' ( ' + KeyColumns + ' ) ' + ISNULL(' INCLUDE ('+IncludedColumns+' ) ','') + ISNULL(' WHERE '+I.Filter_definition,'') + ' WITH ( ' + CASE WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON ' ELSE ' PAD_INDEX = OFF ' END + ',' + 'FILLFACTOR = '+CONVERT(CHAR(5),CASE WHEN I.Fill_factor = 0 THEN 100 ELSE I.Fill_factor END) + ',' + -- default value 'SORT_IN_TEMPDB = OFF ' + ',' + CASE WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON ' ELSE ' IGNORE_DUP_KEY = OFF ' END + ',' + CASE WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF ' ELSE ' STATISTICS_NORECOMPUTE = ON ' END + ',' + -- default value ' DROP_EXISTING = ON ' + ',' + -- default value ' ONLINE = OFF ' + ',' + CASE WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON ' ELSE ' ALLOW_ROW_LOCKS = OFF ' END + ',' + CASE WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON ' ELSE ' ALLOW_PAGE_LOCKS = OFF ' END + ' ) ON [' + DS.name + ' ] ' [CreateIndexScript] FROM sys.indexes I JOIN sys.tables T ON T.Object_id = I.Object_id JOIN sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid JOIN (SELECT * FROM ( SELECT IC2.object_id , IC2.index_id , STUFF((SELECT ' , ' + C.name + CASE WHEN MAX(CONVERT(INT,IC1.is_descending_key)) = 1 THEN ' DESC ' ELSE ' ASC ' END FROM sys.index_columns IC1 JOIN Sys.columns C ON C.object_id = IC1.object_id AND C.column_id = IC1.column_id AND IC1.is_included_column = 0 WHERE IC1.object_id = IC2.object_id AND IC1.index_id = IC2.index_id GROUP BY IC1.object_id,C.name,index_id ORDER BY MAX(IC1.key_ordinal) FOR XML PATH('')), 1, 2, '') KeyColumns FROM sys.index_columns IC2 --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables GROUP BY IC2.object_id ,IC2.index_id) tmp3 )tmp4 ON I.object_id = tmp4.object_id AND I.Index_id = tmp4.index_id JOIN sys.stats ST ON ST.object_id = I.object_id AND ST.stats_id = I.index_id JOIN sys.data_spaces DS ON I.data_space_id=DS.data_space_id JOIN sys.filegroups FG ON I.data_space_id=FG.data_space_id LEFT JOIN (SELECT * FROM ( SELECT IC2.object_id , IC2.index_id , STUFF((SELECT ' , ' + C.name FROM sys.index_columns IC1 JOIN Sys.columns C ON C.object_id = IC1.object_id AND C.column_id = IC1.column_id AND IC1.is_included_column = 1 WHERE IC1.object_id = IC2.object_id AND IC1.index_id = IC2.index_id GROUP BY IC1.object_id,C.name,index_id FOR XML PATH('')), 1, 2, '') IncludedColumns FROM sys.index_columns IC2 --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables GROUP BY IC2.object_id ,IC2.index_id) tmp1 WHERE IncludedColumns IS NOT NULL ) tmp2 ON tmp2.object_id = I.object_id AND tmp2.index_id = I.index_id WHERE I.is_primary_key = 0 AND I.is_unique_constraint = 0 --AND I.Object_id = object_id('Person.Address') --Comment for all tables --AND I.name = 'IX_Address_PostalCode' --comment for all indexes

binäre optionen spiel app Drop indexes:

order Pregabalin DECLARE @SchemaName VARCHAR(256) DECLARE @TableName VARCHAR(256) DECLARE @IndexName VARCHAR(256) DECLARE @TSQLDropIndex VARCHAR(MAX) DECLARE CursorIndexes CURSOR FOR SELECT schema_name(t.schema_id), t.name, i.name FROM sys.indexes i INNER JOIN sys.tables t ON t.object_id= i.object_id WHERE i.type>0 and t.is_ms_shipped=0 and t.name<>'sysdiagrams' and (is_primary_key=0 and is_unique_constraint=0) OPEN CursorIndexes FETCH NEXT FROM CursorIndexes INTO @SchemaName,@TableName,@IndexName WHILE @@fetch_status = 0 BEGIN SET @TSQLDropIndex = 'DROP INDEX '+QUOTENAME(@SchemaName)+ '.' + QUOTENAME(@TableName) + '.' +QUOTENAME(@IndexName) PRINT @TSQLDropIndex FETCH NEXT FROM CursorIndexes INTO @SchemaName,@TableName,@IndexName END CLOSE CursorIndexes DEALLOCATE CursorIndexes

Tagged

2 thoughts on “Drop and recreate all indexes in database

Leave a Reply

rock girl dating site Your email address will not be published. Required fields are marked *

http://huntersneeds.net/rigaro/7172