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
Leave a Reply