Categories
Scripts

Index Details for Tables

This script will list out the details of the indexes in a table.This is compatible to SQL 2000 and 2005.

Script

CREATE TABLE #indexdetails (tablename VARCHAR(200),indexname VARCHAR(300), indexdesc VARCHAR(300), indexkeys VARCHAR(300))
DECLARE @tablename VARCHAR(100)
DECLARE index_cursor CURSOR FOR
SELECT name FROM sysobjects WHERE xtype = 'U'
OPEN index_cursor
FETCH NEXT FROM index_cursor
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
	INSERT INTO #indexdetails (indexname,indexdesc,indexkeys) EXEC sp_helpindex @tablename
	UPDATE #indexdetails SET tablename = @tablename WHERE tablename IS NULL
FETCH NEXT FROM index_cursor
INTO @tablename
END
CLOSE index_cursor
DEALLOCATE index_cursor
SELECT * FROM #indexdetails ORDER BY tablename
DROP TABLE #indexdetails

Leave a Reply

Leave a Reply

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

*