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_nameSYSNAME,
@ind_descVARCHAR(210),
@ind_keysNVARCHAR(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