Categories
Scripts

Find Indexed column details in a database

I was looking for a script to find out the indexed column details as part of our migration. I tried to use sp_helpindex however it requires a object name which means you can find details for only one table at a time. My requirement is to get details about all indexed columns across the database so I came up with this script. Let me know your views.

USE dbname
GO
SET NOCOUNT ON
DECLARE @SQL VARCHAR(4000),@tid INT, @iname VARCHAR(500)
DECLARE @ind TABLE (tableid INT,indexname VARCHAR(1000),keycolumn VARCHAR(1000))
DECLARE @result TABLE (tableid INT,indexname VARCHAR(1000),keycolumn VARCHAR(4000))
INSERT @ind
SELECT a.object_id AS tableid
,c.name AS indexname
,b.name AS keycolumn
FROM sys.index_columns a join sys.columns b ON
a.object_id=b.object_id and a.column_id = b.column_id
join sys.indexes c ON a.index_id =c.index_id and a.object_id = c.object_id
and OBJECTPROPERTY (a.object_id,'IsSystemTable')=0
DECLARE collist CURSOR FOR
SELECT DISTINCT tableid,indexname FROM @ind ORDER BY tableid
OPEN collist
FETCH NEXT FROM collist INTO @tid,@iname
WHILE(@@FETCH_STATUS=0)
BEGIN
INSERT @result
SELECT @tid,@iname,
STUFF((SELECT ','+keycolumn FROM @ind where tableid=@tid and indexname = @iname FOR XML PATH ('')),1,1,'')
PRINT @sql
FETCH NEXT FROM collist INTO @tid,@iname
END
CLOSE collist
DEALLOCATE collist
SELECT OBJECT_NAME(tableid) TableName
,indexname
,CASE INDEXPROPERTY(tableid,indexname,'IsClustered') WHEN 1 THEN 'Clustered' ELSE 'NonClustered or XML Index' END
,keycolumn  FROM @result
ORDER BY TableName

I have tested it in my machine and the result is below

indexed_columns

Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

*