Index Related Question

• KGJ-Dev

  SSCertifiable

  Points: 5780

  More actions

  January 15, 2017 at 3:21 pm

  #325466

  Hi,

  I have a table as below
  Create Table Tables(Id int identity(1,1),TableName varchar(50),SchemaName nvarchar(20))
  
  This table contains tablename where i need to generate Create index script(non clustered). Assume i have 5 table name in my above table and  those 5 tables already in my database with proper schema. i need to generate the "Create non clustered index script ". i googled through and got the below query
   
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

  This gives all the non clustered index of my datababse. But how can i get just the tablename in the table "Tables"

  In the above query i can see 
  
 --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables  
--AND I.Object_id = object_id('Person.Address') --Comment for all tables 

  
  i tried to uncomment those lines and to test just gave one table name. But it doesn't generate. Can anyone please  give me working script to generate create index script for specific tables. Please note i need to use sql query not using IDE generate script option

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  January 16, 2017 at 2:51 am

  #1922914

  The script you posted works.

  

  Check the name you're putting in the WHERE clause, and check if your DB is case sensitive, if it is the case will matter too.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• KGJ-Dev

  SSCertifiable

  Points: 5780

  More actions

  January 16, 2017 at 5:38 am

  #1922950

  GilaMonster - Monday, January 16, 2017 2:51 AM

  The script you posted works.

  

  Check the name you're putting in the WHERE clause, and check if your DB is case sensitive, if it is the case will matter too.

  Hi Hilary,

  thank you for your reply and if i don't put anything in the where condition this works. pulling all the tables script. But if i put condition it's not doing. below the sample you can play with. Please use your tablename in the table variable insert.
  
  
declare @mytables table(tablename nvarchar(50),schemaname nvarchar(20));
insert into @mytables(tablename,schemaname)
select 'student','dbo';

  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
join @mytables x on x.tablename=object_name(ic2.object_id )
join sys.schemas ss on ss.name=x.schemaname
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 
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
join @mytables x on x.tablename=object_name(ic2.object_id )
join sys.schemas ss on ss.name=x.schemaname
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 indexe

   

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  January 16, 2017 at 5:48 am

  #1922952

  That code throws an error.
  Msg 4104, Level 16, State 1, Line 66
  The multi-part identifier "ic2.object_id" could not be bound.

  Can you post the working code that you're using and is not returning anything?
  Why are you messing with a table variable with the names in?

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• KGJ-Dev

  SSCertifiable

  Points: 5780

  More actions

  January 16, 2017 at 7:52 am

  #1922984

  GilaMonster - Monday, January 16, 2017 5:48 AM

  That code throws an error.
  Msg 4104, Level 16, State 1, Line 66
  The multi-part identifier "ic2.object_id" could not be bound.

  Can you post the working code that you're using and is not returning anything?
  Why are you messing with a table variable with the names in?

  finally this is working for me 

  
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 
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
join @mytables x on x.tablename=object_name(I.Object_id )
join sys.schemas ss on ss.name=x.schemaname
WHERE I.is_primary_key = 0 AND I.is_unique_constraint = 0