I'm trying to find out if a particular index name "Test" exists in all databases using below code. The result I'm getting is always "NOT EXIST" for all databases in an instance. However, the index does indeed exist on few of the databases. Can someone please point out where the code is wrong?
DECLARE @Name AS VARCHAR(100)
DECLARE dbCur CURSOR FOR
SELECT name FROM SYS.databases where name not in ('master','msdb','model','tempdb')
OPEN dbCur
FETCH NEXT FROM dbCur
INTO @Name
WHILE @@FETCH_STATUS = 0
BEGIN
--Check if index exist
EXECUTE('USE [' + @Name + ']')
If 1 = ((SELECT COUNT(*) as index_count
FROM sys.indexes
WHERE object_id = OBJECT_ID('dbo.TestTable')
AND name = 'Test'))
Print 'INDEX EXIST ' + @Name;
ELSE
Print 'INDEX DOES NOT EXIST ' + @Name;
FETCH NEXT FROM dbCur
INTO @Name
END
CLOSE dbCur
DEALLOCATE dbCur
Result:
INDEX DOES NOT EXIST DB1
INDEX DOES NOT EXIST DB2
INDEX DOES NOT EXIST DB3
INDEX DOES NOT EXIST DB4