Home » SQL Server

List Indexes in SQL Server

18. August 2009 by Admin 2 Comments

The following SQL will list all indexes in within the database that you run the sql script.  I was looking for the option within Visual Studio - Data Dude add-on that displayed index differences between two databases.  I eventually discovered the line by line index comparison by exploding the Table-Indexes however I was unable to create the newly added script index in the 2nd database.  Is this possible?

In lieu of the shortcomings within Data-Dude I used the following script to manually compare and script the indexes in both databases.

DECLARE GetTables CURSOR READ_ONLY
FOR
  SELECT TABLE_NAME
  FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_TYPE = 'BASE TABLE'
  AND OBJECTPROPERTY (OBJECT_ID(TABLE_NAME), 'IsMSShipped') = 0

DECLARE @TableName sysname
OPEN GetTables

FETCH NEXT FROM GetTables INTO @TableName
WHILE (@@fetch_status = 0)
BEGIN
  EXEC sp_helpindex @TableName
  FETCH NEXT FROM GetTables INTO @TableName
END

CLOSE GetTables
DEALLOCATE GetTables


Comments (2) -

Bonus für Black Jack said:

I found your site on delicious today and really liked it.. i bookmarked it and will be back to check it out some more later ..  You made me late for work reading this. Nice blog,I eventually discovered the line by line index comparison by exploding the Table-Indexes however I was unable to create the newly added script index in the 2nd database.

United States Caridad Pietrzak said:

Hello, I enjoy reading through your post. I wanted to write a little comment to support you.

Add comment



  Country flag
biuquote
  • Comment
  • Preview
Loading







Current HOT Blog List...

These are blog entries that I have found interesting and are worth checking out.
Forbidden