Tuesday, October 20, 2015

Generate Script for all Non-Cluster Indexes in a database

you can't generate script for all Non-cluster indexes (Script for only Non-cluster indexes) by right on database --> tasks --> Generate Scripts. It will generate scripts including table schema. To generate only non-clusterindexes use below script. 



using  below script you can generate script for all Non Cluster indexes in a database 




declare @EmptyString varchar(1)
select @EmptyString = ''

-- 35 is the lenght of the name field of the master.dbo.spt_values table
declare @IgnoreDuplicateKeys varchar(35),
    @Unique varchar(35),
    @IgnoreDuplicateRows varchar(35),
    @Clustered varchar(35),
    @Hypotethical varchar(35),
    @Statistics varchar(35),
    @PrimaryKey varchar(35),
    @UniqueKey varchar(35),
    @AutoCreate varchar(35),
    @StatsNoRecompute varchar(35)

select @IgnoreDuplicateKeys = name from master.dbo.spt_values 
    where type = 'I' and number = 1 --ignore duplicate keys
select @Unique = name from master.dbo.spt_values 
    where type = 'I' and number = 2 --unique
select @IgnoreDuplicateRows = name from master.dbo.spt_values 
    where type = 'I' and number = 4 --ignore duplicate rows
select @Clustered = name from master.dbo.spt_values 
    where type = 'I' and number = 16 --clustered
select @Hypotethical = name from master.dbo.spt_values 
    where type = 'I' and number = 32 --hypotethical
select @Statistics = name from master.dbo.spt_values 
    where type = 'I' and number = 64 --statistics
select @PrimaryKey = name from master.dbo.spt_values 
    where type = 'I' and number = 2048 --primary key
select @UniqueKey = name from master.dbo.spt_values 
    where type = 'I' and number = 4096 --unique key
select @AutoCreate = name from master.dbo.spt_values 
    where type = 'I' and number = 8388608 --auto create
select @StatsNoRecompute = name from master.dbo.spt_values 
    where type = 'I' and number = 16777216 --stats no recompute
select 'CREATE NONCLUSTERED INDEX ['+i.name+'] ON [dbo].['+o.name+'] ('+
  case when index_col(o.name,indid, 1) IS NULL then '' else '[' + index_col(o.name,indid, 1) + '] ASC'end +''+
  case when index_col(o.name,indid, 2)IS NULL then ''  else +',['+index_col(o.name,indid, 2)+ '] ASC'end+''+
  case when index_col(o.name,indid, 3)IS NULL then '' else +',['+index_col(o.name,indid, 3)+ '] ASC'end+''+
  case when index_col(o.name,indid, 4)IS NULL then '' else +',['+index_col(o.name,indid, 4)+ '] ASC'end+''+
  case when index_col(o.name,indid, 5)IS NULL then '' else +',['+index_col(o.name,indid, 5)+ '] ASC'end+''+
  case when index_col(o.name,indid, 6)IS NULL then '' else +',['+index_col(o.name,indid, 6)+ '] ASC'end+''+
  case when index_col(o.name,indid, 7)IS NULL then '' else +',['+index_col(o.name,indid, 7)+ '] ASC'end+''+
  case when index_col(o.name,indid, 8)IS NULL then '' else +',['+index_col(o.name,indid, 8)+ '] ASC'end+''+
  case when index_col(o.name,indid, 9)IS NULL then '' else +',['+index_col(o.name,indid, 9)+ '] ASC'end+''+
  case when index_col(o.name,indid, 10)IS NULL then '' else +',['+index_col(o.name,indid, 10)+ '] ASC'end+''+
  case when index_col(o.name,indid, 11)IS NULL then '' else +',['+index_col(o.name,indid, 11)+ '] ASC'end+''+
  case when index_col(o.name,indid, 12)IS NULL then '' else +',['+index_col(o.name,indid, 12)+ '] ASC'end+''+
  case when index_col(o.name,indid, 13)IS NULL then '' else +',['+index_col(o.name,indid, 13)+ '] ASC'end+''+
  case when index_col(o.name,indid, 14)IS NULL then '' else +',['+index_col(o.name,indid, 14)+ '] ASC'end+''+
  case when index_col(o.name,indid, 15)IS NULL then '' else +',['+index_col(o.name,indid, 15)+ '] ASC'end+''+
  case when index_col(o.name,indid, 16)IS NULL then '' else +',['+index_col(o.name,indid, 16)+ '] ASC'end+''+') ON [Primary]'  
from sysindexes i, sysobjects o
where i.id = o.id and
      indid >1 and indid < 255 --all the clustered (=1), non clusterd (>1 and <251), and text or image (=255) 
      and o.type = 'U' --user table
      --ignore the indexes for the autostat
      and (i.status & 64) = 0 --index with duplicates
      and (i.status & 8388608) = 0 --auto created index
      and (i.status & 16777216)= 0 --stats no recompute
      order by o.name