Technical Article

Finding All Index Info for Small Databases

,

This is a simple script which would list sp_helpindex information for all the tables in the database. You dont have to provide any table name or anything. Just run it on the database from which you want to retrieve the information.

This is helpful when you have several servers running the same form of DB, and you want to compare the index information. I used it to compare 7 different servers running the same database with a model database. I wanted to compare what indexes are there on what columns. You can transfer the results from every server to an excel sheet and compare each table's indexes across servers.

BEGIN TRANSACTION
-- this table can be used to store info for later use also, but I am dropping it at the end.
CREATE TABLE dbo.indinfo
	(
	tableName NVARCHAR(776) NULL,
	indexName SYSNAME NULL,
	indexDescription VARCHAR(210) NULL,
	indexKeys NVARCHAR(2078) NULL
	)  ON [PRIMARY]
COMMIT TRANSACTION
GO
DECLARE @TABLE_NAME NVARCHAR(776)

DECLARE TABLECURSUR CURSOR FAST_FORWARD READ_ONLY FOR 
SELECT table_name FROM INFORMATION_SCHEMA.Tables 
WHERE TABLE_TYPE = 'BASE TABLE' order by table_name asc
OPEN TABLECURSUR
FETCH NEXT FROM TABLECURSUR INTO @TABLE_NAME

WHILE @@FETCH_STATUS = 0
BEGIN
	CREATE TABLE #indinfo2
	(
	indexName SYSNAME NULL,
	indexDescription VARCHAR(210) NULL,
	indexKeys NVARCHAR(2078) NULL
	)
	INSERT #indinfo2 EXEC SP_HELPINDEX @TABLE_NAME
	
	DECLARE @ind_name	SYSNAME,
		@ind_desc	VARCHAR(210),
		@ind_keys	NVARCHAR(2078)

	DECLARE indname CURSOR FAST_FORWARD READ_ONLY FOR 
	SELECT indexName, indexdescription, indexkeys FROM #indinfo2
	OPEN indname
	FETCH NEXT FROM indname INTO @ind_name,@ind_desc, @ind_keys
	WHILE @@fetch_status = 0
	BEGIN
		INSERT indinfo VALUES(@table_name, @ind_name,@ind_desc, @ind_keys)
	FETCH NEXT FROM indname INTO @ind_name,@ind_desc, @ind_keys
	END
	CLOSE  indname
	DEALLOCATE indname
	DROP TABLE #indinfo2
	
FETCH NEXT FROM TABLECURSUR INTO @TABLE_NAME
END
CLOSE TABLECURSUR
DEALLOCATE TABLECURSUR
GO
SELECT * FROM indinfo
GO
DROP TABLE indinfo

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating