kyZzr5qZ

1Database Without index IX_Missing_Index
2    ----------------------------------------------
3    Company_2
4    Company_3
5	
6--Create a global temp table
7create table ##MissingIndex (DbName sysname)
8
9--Run the following query to generate dynamic sql statements
10--NOTE: Change **MissingIndexName** to your index name
11select ' insert into ##MissingIndex select ''' + name + ''' where not exists (select * from ' + name + '.sys.indexes where name=''**MissingIndexName**'')' from sys.databases where database_id>4
12
13--run the generated statements
14
15--Then
16select * from ##missingindex order by DbName
17	
18CREATE TABLE #dbs(db sysname);
19
20DECLARE @cmd nvarchar(max) = N'INSERT #dbs SELECT ''?''
21  WHERE EXISTS (SELECT 1 FROM ?.sys.tables WHERE name = N''TableA'')
22  AND NOT EXISTS (SELECT 1 FROM ?.sys.indexes AS i
23    INNER JOIN ?.sys.tables AS t
24    ON i.[object_id] = t.[object_id]
25    WHERE t.name = N''TableA''
26      AND i.name = N''IX_Missing_Index'');';
27
28EXEC master.dbo.sp_foreachdb @command = @cmd, @user_only = 1;
29
30SELECT db FROM #dbs;
31
32DROP TABLE #dbs;
33	
34SET NOCOUNT ON
35SET XACT_ABORT ON
36SET DEADLOCK_PRIORITY NORMAL;
37SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
38
39    DECLARE  @sql NVARCHAR(MAX)
40            ,@vCrlf CHAR(2);
41
42    SELECT  @vCrlf = CHAR(13)+CHAR(10);
43
44select @sql = 'select DATABASE_NAME=db_name(), * from sys.indexes where name like ''IDX_MY_INDEX'''
45
46
47SET @SQL = (
48            SELECT STUFF(
49  (SELECT N'  ' + ' USE ' + QUOTENAME(name) +';' + @vCrlf + @SQL + @vCrlf    
50                                FROM SYS.DATABASES SD
51                                WHERE SD.STATE_DESC = 'ONLINE' -->Skips the database if it is not online
52                                  AND SD.COMPATIBILITY_LEVEL > 80  
53
54      FOR XML PATH(''),TYPE)
55  .value('text()[1]','nvarchar(max)'),1,2,N'')
56)
57
58EXECUTE MASTER.DBO.sp_executesql @SQL