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 comment

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

*

´╗┐
x

Related Posts

Backup Report
I was working on one the project and they were looking for a backup report which should specify whether it's physical or logical and few more det...
T-SQL Script to find the names of the StoredProcedure that has used dynamic SQL
This article has the script that will be useful to find the names of the  Stored procedure that has used dynamic sql within the definition of the...
Retrieve Auditing Configuration Details
Script to retrieve auditing configuration details in sql server. Auditing feature is released from SQL Server 2008 onward so if you have utilized...
powered by RelatedPosts