Friday, 17 February 2017

Find Index Name and columns



SELECT  S.NAME SCHEMA_NAME ,
        T.NAME TABLE_NAME ,
        I.NAME INDEX_NAME ,
        C.NAME COLUMN_NAME
FROM    SYS.TABLES T
        INNER JOIN SYS.SCHEMAS S ON T.SCHEMA_ID = S.SCHEMA_ID
        INNER JOIN SYS.INDEXES I ON I.OBJECT_ID = T.OBJECT_ID
        INNER JOIN SYS.INDEX_COLUMNS IC ON IC.OBJECT_ID = T.OBJECT_ID
        INNER JOIN SYS.COLUMNS C ON C.OBJECT_ID = T.OBJECT_ID
                                    AND IC.INDEX_ID = I.INDEX_ID
                                    AND IC.COLUMN_ID = C.COLUMN_ID
WHERE t.name = 'name'
ORDER BY I.NAME ,
        I.INDEX_ID ,
        IC.KEY_ORDINAL

GO

SELECT '['+Sch.name+'].['+ Tab.[name]+']' AS TableName,
Ind.[name] AS IndexName,
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 = 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
FROM sys.[indexes] 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]
WHERE ind.name like 'idx_name_id_first_middle_last%'
ORDER BY TableName

No comments:

Post a Comment

How to find out recent differential backup history in SQL Server

-- Get Backup History for required database SELECT TOP 100 s.database_name, m.physical_device_name, CAST(CAST(s.backup_size / 1000000...