Often we send a database to a client and then later on find out that a key got dropped or an index removed along the way… Well, now we can easily test this!
Open up Index Checker 1.0 and you’ll see the following SQL:
-- ============================================= -- Author: Arnold, Brad -- Create date: 12/16/2013 -- Description: Index Checker 1.0 -- ============================================= --Create some variables that we'll use later DECLARE @ID varchar(50) DECLARE @TableName varchar(255) DECLARE @IndexName varchar(255) --I didn't want to see the (### row(s) affected) in the output window so we turn that off. SET NOCOUNT ON; --We're going to select the information on the indexes from sys.indexes in SQL Server and insert it into a temp table (#TEMP). --We're using NewID() and inserting a GUID in the ID column so that we have a truly unique value for each row. --There are some extra columns in this SELECT but I left them because it's nice to run that --manually and see the data if you are investigating a missing index. SELECT NewID() AS ID, SchemaName = schema_name(schema_id), TableName = object_name(t.object_id), ColumnName = c.name, [Unique] = CASE WHEN i.is_unique = 1 THEN 'Yes' ELSE 'No' END, [Clustered] = CASE WHEN i.index_id = 1 THEN 'C' ELSE 'NC' END, Ordinal = key_ordinal, IndexName = i.Name INTO #TEMP FROM sys.indexes i INNER JOIN sys.tables t ON i.object_id = t.object_id INNER JOIN sys.index_columns ic ON ic.object_id = t.object_id AND ic.index_id = i.index_id INNER JOIN sys.columns c ON c.object_id = t.object_id AND ic.column_id = c.column_id ORDER BY SchemaName, TableName, IndexName, Key_Ordinal --We set NOCOUNT back to OFF so that future executes will show the (### row(s) affected) message. SET NOCOUNT OFF; --Now we're going to use a cursed CURSOR to loop through all the tables and all the indexes for each table in the database. --The output will be PRINT statements that will then be saved/sent to the client to run against their database. --The print statement will do a IF NOT EXISTS and check for the index existence. DECLARE MY_CURSOR CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR SELECT ID FROM #TEMP OPEN MY_CURSOR FETCH NEXT FROM MY_CURSOR INTO @ID WHILE (@@FETCH_STATUS = 0) BEGIN SELECT @TableName = TableName FROM #TEMP WHERE ID = @ID SELECT @IndexName = IndexName FROM #TEMP WHERE ID = @ID PRINT 'IF NOT EXISTS(SELECT * FROM sys.indexes WHERE object_id = object_id('''+@TableName+''') AND NAME ='''+@IndexName+''') PRINT ''The missing index is from: '+@TableName+' table and the index name is: '+@IndexName+'''' --PRINT @ID + ' | ' + @TableName + ' | ' + @IndexName FETCH NEXT FROM MY_CURSOR INTO @ID END --Cleanup by closing the CURSOR, deallocating the CURSOR and dropping the temp table. CLOSE MY_CURSOR DEALLOCATE MY_CURSOR DROP TABLE #TEMP
The above code is pretty commented but the basics are as follows.
If you run that script the output window will be a ton of SQL statements:
CTRL-A to highlight it all and then paste it into a new window, notepad, whatever…. Save the output whatever you want (
You email that SQL file to the client and they run it against their NetTraxNet database…
If the client is missing an index it will tell them exactly what one(s):
We can then generate the scripts for them and send them the scripts to re-create the indexes!
If they are OK and nothing is missing they will see this: