Monday, 30 September 2019

How to get DROP AND CREATE script for all INDEXES USING simple SELECT in SQL SERVER

Below T-SQL QUERY helps to get DROP and CREATE script for all INDEXES in SQL SERVER. Feel free to use it if needed,  Kindly let me know in case if you got a better and easy way of doing it (do not say 😊 that it can be done using SSMS, right-click on the database-->Tasks-->Generate Scripts...).

SELECT INDEX_NAME,'IF EXISTS (SELECT * FROM SYS.INDEXES WHERE NAME = '''+INDEX_NAME+''') DROP INDEX '+QUOTENAME(INDEX_NAME)+' ON ' + QUOTENAME(TABLE_SCHEMA) +'.'+ QUOTENAME(TABLE_NAME)+
' CREATE '+ IS_UNIQUE  +INDEX_TYPE_DESC + ' INDEX ' +QUOTENAME(INDEX_NAME)+' ON ' + QUOTENAME(TABLE_SCHEMA) +'.'+ QUOTENAME(TABLE_NAME)+ '('+[INDEX_COLUMNS]+' ) '+ 
  case when [INCLUDED_COLUMNS]<>'' then CHAR(13) +'INCLUDE (' + [INCLUDED_COLUMNS]+ ')' else '' end + CHAR(13)+'WITH (' + INDEXOPTIONS+ ') ON ' + QUOTENAME(FILEGROUPNAME) + ';' SQLCMD
 FROM ( 
SELECT DISTINCT SCHEMA_NAME(ST.SCHEMA_ID) TABLE_SCHEMA, ST.NAME TABLE_NAME, SIX.NAME INDEX_NAME,
CASE WHEN SIX.IS_UNIQUE = 1 THEN ' UNIQUE ' ELSE '' END AS IS_UNIQUE 
, SIX.TYPE_DESC COLLATE DATABASE_DEFAULT  INDEX_TYPE_DESC, 
  CASE  WHEN SIX.IS_PADDED=1 THEN 'PAD_INDEX = ON, ' ELSE 'PAD_INDEX = OFF, ' END 
 + CASE WHEN SIX.ALLOW_PAGE_LOCKS=1 THEN 'ALLOW_PAGE_LOCKS = ON, ' ELSE 'ALLOW_PAGE_LOCKS = OFF, ' END
 + CASE WHEN SIX.ALLOW_ROW_LOCKS=1 THEN  'ALLOW_ROW_LOCKS = ON, ' ELSE 'ALLOW_ROW_LOCKS = OFF, ' END
 + CASE WHEN INDEXPROPERTY(ST.OBJECT_ID, SIX.NAME, 'ISSTATISTICS') = 1 THEN 'STATISTICS_NORECOMPUTE = ON, ' ELSE 'STATISTICS_NORECOMPUTE = OFF, ' END
 + CASE WHEN SIX.IGNORE_DUP_KEY=1 THEN 'IGNORE_DUP_KEY = ON, ' ELSE 'IGNORE_DUP_KEY = OFF ' END
 --+ ', SORT_IN_TEMPDB = OFF '+ CASE WHEN IX.FILL_FACTOR > 0 THEN ',FILLFACTOR =' + CAST(IX.FILL_FACTOR AS VARCHAR(3)) ELSE ' ' END 
 AS INDEXOPTIONS,FILEGROUP_NAME(SIX.DATA_SPACE_ID) FILEGROUPNAME 
 ,STUFF(REPLACE((SELECT '~~' + QUOTENAME(COL.NAME)+ ' '+ CASE WHEN IXC.IS_DESCENDING_KEY =1 THEN 'DESC' ELSE 'ASC' END  AS 'data()'
 FROM SYS.TABLES T 
JOIN SYS.INDEXES IX ON T.OBJECT_ID=IX.OBJECT_ID 
JOIN SYS.INDEX_COLUMNS IXC ON IX.OBJECT_ID=IXC.OBJECT_ID AND IX.INDEX_ID= IXC.INDEX_ID 
JOIN SYS.COLUMNS COL ON IXC.OBJECT_ID =COL.OBJECT_ID  AND IXC.COLUMN_ID=COL.COLUMN_ID 
  WHERE IX.TYPE>0 AND (IX.IS_PRIMARY_KEY=0 AND IX.IS_UNIQUE_CONSTRAINT=0) AND IXC.IS_INCLUDED_COLUMN <> 1
AND SCHEMA_NAME(T.SCHEMA_ID) = SCHEMA_NAME(ST.SCHEMA_ID) AND T.NAME = ST.NAME AND IX.NAME = SIX.NAME
FOR XML PATH('')),'~~',', '), 1, 2, '') as [INDEX_COLUMNS] 
,ISNULL(STUFF(REPLACE((SELECT '~~' + QUOTENAME(COL.NAME) AS 'data()' FROM SYS.TABLES T 
JOIN SYS.INDEXES IX ON T.OBJECT_ID=IX.OBJECT_ID 
JOIN SYS.INDEX_COLUMNS IXC ON IX.OBJECT_ID=IXC.OBJECT_ID AND IX.INDEX_ID= IXC.INDEX_ID 
JOIN SYS.COLUMNS COL ON IXC.OBJECT_ID =COL.OBJECT_ID  AND IXC.COLUMN_ID=COL.COLUMN_ID 
  WHERE IX.TYPE>0 AND (IX.IS_PRIMARY_KEY=0 AND IX.IS_UNIQUE_CONSTRAINT=0) AND IXC.IS_INCLUDED_COLUMN = 1
AND SCHEMA_NAME(T.SCHEMA_ID) = SCHEMA_NAME(ST.SCHEMA_ID) AND T.NAME = ST.NAME AND IX.NAME = SIX.NAME
FOR XML PATH('')),'~~',', '), 1, 2, ''),'') as [INCLUDED_COLUMNS] 
 FROM SYS.TABLES ST INNER JOIN SYS.INDEXES SIX ON ST.OBJECT_ID=SIX.OBJECT_ID 
 WHERE SIX.TYPE>0 AND ( SIX.IS_PRIMARY_KEY=0  AND SIX.IS_UNIQUE_CONSTRAINT=0) 
 AND ST.IS_MS_SHIPPED=0 AND ST.NAME<>'SYSDIAGRAMS'  ) A

























Thank you,
Narasimha

No comments:

Post a comment