How to list all indexes in Database programatically. 1

[borntorun]

Hi,

I'd like to list all indexes in a sql server database programatically. I have done this before but can't seem to find the sp i thought i used.

Thanks.

 

[JayKusch]

Try This ... give a bit more info but you may find it usefull ...

DROP TABLE #Monitor_Tables_and_Indexes
SET	NOCOUNT ON 

DECLARE	@DBName		VarChar(50)
DECLARE	@SQL1		nVarChar(800)
DECLARE	@SQL2		nVarChar(800)
DECLARE	@SQL3		nVarChar(800)

DECLARE	@Table_ID	Int
DECLARE	@Table_Name	VarChar(400)

DECLARE	@Record_Cnt	TinyInt
DECLARE	@Record_Max	TinyInt

SET	@Record_Cnt	= 1
SET	@Record_Max	= 0

-- Create Temp Table to hold Database Names to report on
CREATE #Monitor_Tables_and_Indexes (
	[MTI_ID] [int] IDENTITY (1, 1) NOT NULL ,
	[Database_Name] [varchar] (100) NULL ,
	[Table_Name] [varchar] (100) NULL ,
	[Index_Name] [varchar] (100) NULL ,
	[Index_Description] [varchar] (210) NULL ,
	[Index_Keys] [varchar] (2048) NULL ,
	[Date_Stats_Pulled] [datetime] NULL 
) ON [PRIMARY]
CREATE TABLE #Temp_Table (	Table_Name		VarChar(255)	NOT NULL,
				Table_ID		INT		NULL,
				Has_Indexes		Bit		NULL  )

CREATE TABLE #Table_SP_HelpIndex (	Index_Name		VarChar(255)	NOT NULL,
					Index_Description	VarChar(255)	NULL,
					Index_Keys		VarChar(255)	NULL )

CREATE TABLE #Table_Indexes (	Table_Name		VarChar(255)	NOT NULL,
				Index_Name		VarChar(255)	NOT NULL,
				Index_Description	VarChar(255)	NULL,
				Index_Keys		VarChar(255)	NULL )

CREATE  CLUSTERED  INDEX [IX_Table_Name] ON [dbo].[#Table_Indexes]([Table_Name]) ON [PRIMARY]

CREATE TABLE #DBs_To_Report (
	[DBTR_Key]	[int] IDENTITY (1, 1) NOT NULL ,
	[DBTR_DB_Name]	[varchar] (400) NULL
) ON [PRIMARY]

INSERT INTO #DBs_To_Report

	SELECT	Name
	FROM	Master..SysDatabases
	WHERE	Name	<> 'master'	AND
			Name	<> 'model'	AND
			Name	<> 'msdb'	AND
			Name	<> 'Pubs'	AND
			Name	<> 'Northwind'	AND
			Name	<> 'tempdb'
	ORDER BY	Name

SET	@Record_Max = ( Select Count(*) FROM #DBs_To_Report )

TRUNCATE TABLE Monitor_Tables_and_Indexes

--  Start reporting on the DBs

WHILE	@Record_Cnt <= @Record_Max

  BEGIN
	SET	@dbname = ( SELECT	DBTR_DB_Name
			    FROM	#DBs_To_Report
			    WHERE	DBTR_Key = @Record_Cnt )
	BEGIN
		DELETE FROM #Temp_Table

		SET	@SQL1 = 'SELECT Name, ID, 0 FROM ' + @dbname + '..SysObjects AS so WHERE (so.xtype = ''u'') AND so.Name <> ''dtproperties'' ORDER BY name'

		INSERT INTO #Temp_Table

			EXEC	(@SQL1)

		SET	@SQL2 = 'UPDATE	#Temp_Table SET Has_Indexes = 1 FROM ' + @dbname + '..SysIndexes WHERE (Table_ID = ID) AND ( SUBSTRING(Name,1,3) = ''IX_'' OR SUBSTRING(Name,1,3) = ''PK_'')'

		EXEC	(@SQL2)

		INSERT INTO	#Table_Indexes

			SELECT	Table_Name, 
				'No Index Names for this table',
				'No Index Descriptions for this table',
				'No Index Keys for this table'
			FROM	#Temp_Table
			WHERE	Has_Indexes = 0

		DELETE	#Temp_Table
		WHERE	Has_Indexes = 0

		INSERT INTO #Monitor_Tables_and_Indexes

			SELECT	@dbname,
				Table_Name,
				Index_Name,
				Index_Description,
				Index_Keys,
				GetDate()
			FROM		#Table_Indexes
			WHERE		@dbname IS NOT NULL	AND
					Table_Name <> 'dbo.dtproperties'
			ORDER BY	Table_Name, 
					Index_Name

		TRUNCATE TABLE #Table_Indexes
		TRUNCATE TABLE #Table_SP_HelpIndex
	END

	DECLARE Cursor_Tables CURSOR FOR 
	SELECT	Table_Name
	FROM	#Temp_Table

	OPEN	Cursor_Tables
	FETCH NEXT 
	FROM	Cursor_Tables 
	INTO	@Table_Name

	WHILE @@FETCH_STATUS = 0

	  BEGIN
		SET	@SQL3 = @dbname + '..sp_helpindex ' + REPLACE(@Table_Name,'dbo.','')

		INSERT INTO #Table_SP_HelpIndex

			EXEC	(@SQL3)

		INSERT INTO	#Table_Indexes

			SELECT	@Table_Name, *
			FROM	#Table_SP_HelpIndex

		INSERT INTO Monitor_Tables_and_Indexes

			SELECT	@dbname,
				Table_Name,
				Index_Name,
				Index_Description,
				Index_Keys,
				GetDate()
			FROM		#Table_Indexes
			WHERE		@dbname IS NOT NULL	AND
					Table_Name <> 'dbo.dtproperties'
			ORDER BY	Table_Name, 
					Index_Name

		TRUNCATE TABLE #Table_Indexes
		TRUNCATE TABLE #Table_SP_HelpIndex


		FETCH NEXT FROM Cursor_Tables INTO @Table_Name


	  END

	CLOSE		Cursor_Tables
	DEALLOCATE	Cursor_Tables

	TRUNCATE TABLE #Temp_Table

	SET	@Record_Cnt = @Record_Cnt + 1

  END

UPDATE	#Monitor_Tables_and_Indexes

	SET	Table_Name = REPLACE(Table_Name,'dbo.','')

DROP TABLE #Table_Indexes
DROP TABLE #Temp_Table
DROP TABLE #Table_SP_HelpIndex
DROP TABLE #DBs_To_Report

SELECT #Monitor_Tables_and_Indexes

Thanks

J. Kusch

 

[TonyKhela]

For all indexes in a DB Use:
select name from sysindexes where indid between 1 and 249
OR for indexes for a given table use:
exec sp_helpindex tablename

Hope this helps
TK