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


No comments:

Post a Comment

Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists

If you received below error while attaching a .mdf file in cluster environment please follow below steps to resolve the issue ERROR Ca...