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

C# run powershell script as administrator

Recently I was fetching a problem that I need to run a PowerShell script that will change TFS user Display name and SID. I was trying to run that script from C# that was not working due to TFS security update and TLS certificate. Using this code block I resolve the Issue. var newProcessInfo = new System.Diagnostics.ProcessStartInfo(); newProcessInfo.FileName = @"C:\Windows\SysWOW64\WindowsPowerShell\v1.0\powershell.exe"; newProcessInfo.Verb = "runas"; // Define Run as administrator newProcessInfo.Arguments = script; //Define your powershell script newProcessInfo.UseShellExecute = false; newProcessInfo.RedirectStandardOutput = true; // This will enable to read Powershell run output newProcessInfo.RedirectStandardError = true; Process proces = System.Diagnostics.Process.Start(newProcessInfo); proces.WaitForExit(); // I want to read the output string from powershell window StringBuilder output = new StringBuilder(); output.Append("Started"); while (!proces.St
Post a Comment
Read more

ASP.NET MVC razor SAP Crystal report

Crete a new project: Add a aspx Master Page Create a new folder Reports and 2 sub folder crystal & crystalviewer Now add a web form page in crystalviewer  folder. Add the master page namespace in your web form page. MasterPageFile ="~/Views/Shared/ReportSite.Master" Replace your web form by this code < asp : Content ID ="Content1" ContentPlaceHolderID ="ContentPlaceHolder1" runat ="server">      </ asp : Content > Now go to design mode of your web form drag & drop the crystal report viewer in your web form. After that your page will be look look like this. Replace the code: < CR : CrystalReportViewer ID ="EmployeeList" runat ="server"   HasCrystalLogo ="False"     AutoDataBind ="True"   Height ="50px"   EnableParameterPrompt ="false" EnableDatabaseLogonPrompt
Post a Comment
Read more

mvc razor textboxfor change event change another textboxfor value

Based on value of Weight, Rate , CNF & AWB it will change the value of Freight , TTLCNF anfd TTLFright . Freight= Weight*Rate; TTLCNF  = Weight*CNF; TTLFright=  Freight+ TTLCNF  + AWB; @Html.TextBoxFor(model => model.Weight, new { onChange="return GetWight(this);"}) @Html.TextBoxFor(model => model.Rate, new { onChange="return GetWight(this);"})/Kg @Html.TextBoxFor(model => model.Freight, new {disabled = "disabled" , @readonly = "readonly" ,onChange="return GetTTLFright(this);"}) @Html.TextBoxFor(model => model.CNFPK, new { onChange="return GetCNFPK(this);"}) @Html.TextBoxFor(model => model.TTLCNF, new {disabled = "disabled" , @readonly = "readonly",onChange="return GetTTLFright(this);" }) @Html.TextBoxFor(model => model.AWB, new { onChange="return GetTTLFright(this);"}) and script <script> function GetW
Post a Comment
Read more