Creating NC Indexes Repository

---Get all indexes basic properties

 SELECT  '[' + Sch.name + '].[' + Tab.[name] + ']' AS TableName,

            Ind.type_desc,

            CASE Ind.is_unique WHEN 1 THEN 'UNIQUE' ELSE '' END UniqueOrNot,

            CASE fill_factor WHEN 0 THEN 100 ELSE fill_factor END fill_factor,

        Ind.[name] AS IndexName,

        SUBSTRING(( SELECT  ', [' + AC.name + CASE IC.is_descending_key WHEN

                                                                              1 THEN '] DESC' ELSE ']' END

                    FROM    sys.[tables] AS T

                            INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]

                            INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]

                                                                 AND I.[index_id] = IC.[index_id]

                            INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]

                                                               AND IC.[column_id] = AC.[column_id]

                    WHERE   Ind.[object_id] = I.[object_id]

                            AND Ind.index_id = I.index_id

                            AND IC.is_included_column = 0

                    ORDER BY IC.key_ordinal

                  FOR

                    XML PATH('') ), 2, 8000) AS KeyCols,

        SUBSTRING(( SELECT  ', [' + AC.name +']'

                    FROM    sys.[tables] AS T

                            INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]

                            INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]

                                                                 AND I.[index_id] = IC.[index_id]

                            INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]

                                                               AND IC.[column_id] = AC.[column_id]

                    WHERE   Ind.[object_id] = I.[object_id]

                            AND Ind.index_id = I.index_id

                            AND IC.is_included_column = 1

                    ORDER BY IC.key_ordinal

                  FOR

                    XML PATH('') ), 2, 8000) AS IncludeCols

INTO #ALLindexes                   

FROM    sys.[indexes] AS Ind

        INNER JOIN sys.[tables] AS Tab ON Tab.[object_id] = Ind.[object_id]

        INNER JOIN sys.[schemas] AS Sch ON Sch.[schema_id] = Tab.[schema_id]

            LEFT OUTER JOIN sys.dm_db_index_usage_stats AS usg_stats ON  Ind.index_id = usg_stats.index_id

                                    AND Ind.[OBJECT_ID] = usg_stats.[OBJECT_ID]  and usg_stats.database_id = DB_ID()

WHERE  Ind.type_desc ='NONCLUSTERED'

--AND Tab.name  = 'YourTableNameHere' -- uncomment to get single table indexes detail

AND Ind.is_unique <> 1

ORDER BY TableName




---Get create script as separate column

SELECT *,

'CREATE NONCLUSTERED INDEX ['+IndexName+ '] ON ' + TableName + '('+KeyCols+')' +

 CASE  WHEN IncludeCols IS NOT NULL

                  THEN ' INCLUDE ('+IncludeCols+') WITH (FILLFACTOR = '+CAST(fill_factor AS VARCHAR(10))+')'

                              ELSE ' WITH (FILLFACTOR = '+CAST(fill_factor AS VARCHAR(10))+')' END AS Script

FROM #ALLindexes







---drop table when not required

DROP TABLE #ALLindexes








 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s