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 (NetTraxNet_ClientName_IndexCheck.sql)
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:
