Finding all the Indexes in a SQL Server database

Below is the code for displaying the indexes and corresponding Index name for all the indexes in a database:

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- Declarations
DECLARE @TabName varchar(100)
  , @CNT INT
  , @I INT
  , @ObjectId INT
  , @ValueCoef INT

-- Creating Hash Table for Indexes
CREATE TABLE #T 
(
    ObjectId INT
  , TabName VARCHAR(200)
  , IndexName VARCHAR(200)
  , IndexDescr VARCHAR(200)
  , IndexKeys VARCHAR(200)
  , IndexSize INT
)

-- Selecting the Object ID and Name from Sysobjects
SELECT 
    name
  , ID ObjectId 
INTO #TABLE 
FROM sysobjects 
WHERE xtype = 'U'

-- Setting the Count and Increment Variables
SET @CNT = @@ROWCOUNT
SET @I = 1

-- Adding Identity Column
ALTER TABLE #TABLE ADD ID INT IDENTITY

-- Inserting values for IndexName, Desc and Keys for all indexes in a database
WHILE(@I < = @CNT)
BEGIN -- begin of While Loop
  SELECT 
       @TabName = NAME
     , @ObjectId = ObjectId 
  FROM #TABLE 
  WHERE ID = @I
 
  INSERT INTO #T (IndexName, IndexDescr, IndexKeys)

  EXEC sp_helpindex @TabName

  UPDATE #T SET TabName = @TabName, ObjectId = @ObjectId WHERE TabName IS NULL

  SET @I = @I +1 -- end of While Loop

END

SELECT 
  @ValueCoef = low 
FROM Master.dbo.spt_values 
WHERE number = 1 
  AND type = 'E';

-- Updating IndexSize for the table
UPDATE #T
 SET IndexSize = ((CAST(sysindexes.used AS bigint) * @ValueCoef)/1024)/1024
FROM sysindexes
JOIN #T T
  ON T.IndexName = sysindexes.name
  AND T.ObjectId = sysindexes.id

SELECT 
  * 
FROM #T
ORDER BY TabName, IndexName

GO

--Dropping Temporary tables
DROP TABLE #T
DROP TABLE #TABLE
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: