Skip to main content

SQL Get all Index create script from Database


To get all script from database as a create new index into another database you can use the following


 --Get all Index Script  
 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 = OFF ' + ',' +   
   -- 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


Before run the script For your new database you also need to create file group and file name for index. In my case i give name index for file and file-group name.





Labels:

Comments

Post a Comment

Popular posts from this blog

The calling thread must be STA, because many UI components require this.

Using Thread: // Create a thread Thread newWindowThread = new Thread(new ThreadStart(() => { // You can use your code // Create and show the Window FaxImageLoad obj = new FaxImageLoad(destination); obj.Show(); // Start the Dispatcher Processing System.Windows.Threading.Dispatcher.Run(); })); // Set the apartment state newWindowThread.SetApartmentState(ApartmentState.STA); // Make the thread a background thread newWindowThread.IsBackground = true; // Start the thread newWindowThread.Start(); Using Task and Thread: // Creating Task Pool, Each task will work asyn and as an indivisual thread component Task[] tasks = new Task[3]; // Control drug data disc UI load optimize tasks[0] = Task.Run(() => { //This will handle the ui thread :The calling thread must be STA, because many U...
Post a Comment
Read more

SQL Query Execution time of you in SQL Management Studio

You can check the Execution time of you SQL Query in SQL Management Studio. like this It is very simple that you just put your SQL Query in to the Estimated time execution query DECLARE @StartTime datetime DECLARE @EndTime datetime SELECT @StartTime=GETDATE() -- Write Your Query SELECT @EndTime=GETDATE() --This will return execution time of your query SELECT DATEDIFF(NS,@StartTime,@EndTime) AS [Duration in millisecs]
Post a Comment
Read more

WPF Crystal Report Viewer Using SAP

There is no doubt that we fall a great problem that the VS2010 is not intregated crystal report. Initially it seems to be a big problem. Hare is some step for SAP crystal report that we can use in our WPF application. 1.Download  Crystal report from this Link: http://scn.sap.com/docs/DOC-7824 2 . Remove Crystal report if any exist. 3. Close your VS-2010 and install the new downloaded CRforVS_13_0 . 4. Take a new WPF project   5. Right click on the project click on Properties 6. Change the target framework .NET Framework 4 Client Profile to  .NET Framework 4. 7. Click on main window then click on Toolbox.  Right Click on the General Tab then click on Choose Item. 8. It will appear this window click on WPF Component. 9.  Select CrystalReportsViewer  click on ok   Button. 10. Now you will see the report viewer control. 11. Your Crystal Report Environment is ready. Now we will a...
11 comments
Read more