Index Generation script

Generate_Indexes.sql

SELECT 
	 ind.name 

	,'DROP INDEX ' + QUOTENAME
	(
		CASE WHEN SUBSTRING(ind.name, 1, 2) = 'ix' THEN 'IX' + SUBSTRING(ind.name, 3, 10000) ELSE ind.name END 
	) + ' ON ' + QUOTENAME(sch.name) + '.' + QUOTENAME(obj.name) + '; ' AS DropIndexScript 

	, ' CREATE ' 
	+ CASE WHEN ind.is_unique = 1 THEN ' UNIQUE ' ELSE '' END 
	+  ind.type_desc COLLATE DATABASE_DEFAULT 
	+ ' INDEX ' 
	+  CASE 
		  WHEN ind.name LIKE '%-%' THEN QUOTENAME
			 (
			     CASE WHEN SUBSTRING(ind.name, 1, 2) = 'ix' THEN 'IX' + SUBSTRING(ind.name, 3, 10000) ELSE ind.name END 
			 ) 
		  ELSE 
			CASE WHEN SUBSTRING(ind.name, 1, 2) = 'ix' THEN 'IX' + SUBSTRING(ind.name, 3, 10000) ELSE ind.name END  
	   END 
	+ ' ON ' + sch.name + '.' + obj.name 
	+ ' ( ' + KeyColumns + ' )  ' 
	+ ISNULL(' INCLUDE (' + IncludedColumns + ' ) ', '') 
	+ ISNULL(' WHERE  ' + ind.Filter_definition, '') 
	+ ' WITH (' 
	+ '  PAD_INDEX = ' + CASE WHEN ind.is_padded = 1 THEN 'ON' ELSE 'OFF' END 
	+ ', IGNORE_DUP_KEY = ' + CASE WHEN ind.ignore_dup_key = 1 THEN 'ON' ELSE 'OFF' END 
	+ ', STATISTICS_NORECOMPUTE = ' + CASE WHEN ST.no_recompute = 0 THEN 'OFF' ELSE 'ON' END 
	+ ', FILLFACTOR = ' + CONVERT(char(5), CASE WHEN ind.Fill_factor = 0 THEN 100 ELSE ind.Fill_factor END) 
	-- + ', OPTIMIZE_FOR_SEQUENTIAL_KEY = ' + CASE WHEN ind.optimize_for_sequential_key = 1 THEN 'ON ' ELSE 'OFF' END 
	+ ', SORT_IN_TEMPDB = OFF' -- default value 
	+ ', DROP_EXISTING = OFF' -- default value 
	+ ', ONLINE = OFF' -- default value 

	+ ', ALLOW_ROW_LOCKS = ' + CASE WHEN ind.allow_row_locks = 1 THEN 'ON ' ELSE 'OFF' END 
	+ ', ALLOW_PAGE_LOCKS = ' + CASE WHEN ind.allow_page_locks = 1 THEN 'ON ' ELSE 'OFF' END 
	+ ' ) ON [' + DS.name + ']; ' 
	AS CreateIndexScript 
FROM sys.indexes AS ind 

INNER JOIN sys.objects AS obj ON obj.object_id = ind.object_id 
INNER JOIN sys.schemas AS sch ON sch.schema_id = obj.schema_id 

INNER 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 AS IC1 
						INNER JOIN Sys.columns AS 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 
					,'' 
				) AS KeyColumns 
			FROM sys.index_columns AS ic2 
			-- WHERE ic2.Object_id = object_id('Person.Address') -- Comment for all tables 
			GROUP BY 
				 ic2.object_id 
				,ic2.index_id 

		) AS tmp3 
	) AS tmp4 
	ON tmp4.object_id = ind.object_id 
	AND tmp4.index_id = ind.index_id 

INNER JOIN sys.stats AS ST 
	ON ST.object_id = ind.object_id 
	AND ST.stats_id = ind.index_id 

INNER JOIN sys.data_spaces AS DS 
	ON DS.data_space_id = ind.data_space_id 

INNER JOIN sys.filegroups AS FG 
	ON FG.data_space_id = ind.data_space_id 

LEFT JOIN 
	(
		SELECT *
		FROM 
		(
			SELECT 
				 ic2.object_id 
				,ic2.index_id 
				,STUFF
				(
					(
						SELECT ' , ' + C.name 
						FROM sys.index_columns AS ic1 
						JOIN Sys.columns AS 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 
					,'' 
				) AS IncludedColumns 
			FROM sys.index_columns AS ic2 
			-- WHERE ic2.Object_id = OBJECT_ID('Person.Address') -- Comment for all tables 
			
			GROUP BY 
				 ic2.object_id 
				,ic2.index_id 
			) AS tmp1 

		WHERE IncludedColumns IS NOT NULL 
	) AS tmp2 
	ON tmp2.object_id = ind.object_id 
	AND tmp2.index_id = ind.index_id 

WHERE (1=1) 
AND ind.is_primary_key = 0 
AND ind.is_unique = 0 
AND ind.is_unique_constraint = 0 
AND obj.is_ms_shipped = 0 

-- AND ind.name = 'IX_T_VWS_SVGElement_Code_Gueltigkeit'
-- AND sch.name = 'dbo' 
-- AND obj.name = 'T_VWS_SVGElement'