1

I am working on a Multi Tenant System. In which we have one Manager DB and Each client has a Company DB.

enter image description here

Now all of these database have the same Table Schema.

But some of them have a missing Index on a certain Table.

Is there a way to get a list of Databases with that missing index using SQL? We are using SQL-Server 2012

EDIT:

Let's say Company_1 has an index on TableAcalled IX_Missing_Index - but Company_2 and Company_3 DO NOT have the index 'IX_Missing_Index' - So all I need in the result set is Company_2 and Company_3

e.g. like this

    Database Without index IX_Missing_Index
    ----------------------------------------------
    Company_2
    Company_3
  • Do you want to get list of the indexes which is available in DB Company_1 but not in Company_2 or you want list of missing index that SQL Server optimizer suggest to create on objects? – Rajesh Ranjan Sep 22 '16 at 9:03
  • @RajeshRanjan see my Edit – Dawood Awan Sep 22 '16 at 9:18
  • Are you looking for databases without such an index on TableA or in general? (In other words, some databases may have an index called IX_Missing_Index but on a different table. Should they show up in your query or not?) – Aaron Bertrand Sep 22 '16 at 11:02
1
CREATE TABLE #dbs(db sysname);

DECLARE @cmd nvarchar(max) = N'INSERT #dbs SELECT ''?''
  WHERE EXISTS (SELECT 1 FROM ?.sys.tables WHERE name = N''TableA'')
  AND NOT EXISTS (SELECT 1 FROM ?.sys.indexes AS i
    INNER JOIN ?.sys.tables AS t
    ON i.[object_id] = t.[object_id]
    WHERE t.name = N''TableA''
      AND i.name = N''IX_Missing_Index'');';

EXEC master.dbo.sp_foreachdb @command = @cmd, @user_only = 1;

SELECT db FROM #dbs;

DROP TABLE #dbs;

You can get sp_foreachdb here (it is also being open sourced and added to Brent Ozar Unlimited's SQL Server First Responder Kit).

0

It's a quick one-off kludge, but if you just want to get on with your day:

--Create a global temp table
create table ##MissingIndex (DbName sysname)

--Run the following query to generate dynamic sql statements
--NOTE: Change **MissingIndexName** to your index name
select ' insert into ##MissingIndex select ''' + name + ''' where not exists (select * from ' + name + '.sys.indexes where name=''**MissingIndexName**'')' from sys.databases where database_id>4

--run the generated statements

--Then
select * from ##missingindex order by DbName

I'm sure there are slicker ways using ForEachDB, etc...

0

Just replace IDX_MY_INDEX by your index name.

in my example below I run it with 'IDX_SegmentID_Tier1'which is one index that I have in only 3 of my databases and you can see the picture how it looks like.

SET NOCOUNT ON
SET XACT_ABORT ON
SET DEADLOCK_PRIORITY NORMAL;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    DECLARE  @sql NVARCHAR(MAX)
            ,@vCrlf CHAR(2);

    SELECT  @vCrlf = CHAR(13)+CHAR(10);

select @sql = 'select DATABASE_NAME=db_name(), THE_INDEX_EXIST= CASE WHEN EXISTS (SELECT * from sys.indexes where name like ''IDX_MY_INDEX'')  THEN 1 ELSE 0 END '


SET @SQL = (
            SELECT STUFF(
  (SELECT N'  ' + ' USE ' + QUOTENAME(name) +';' + @vCrlf + @SQL + @vCrlf    
                                FROM SYS.DATABASES SD
                                WHERE SD.STATE_DESC = 'ONLINE' -->Skips the database if it is not online
                                  AND SD.COMPATIBILITY_LEVEL > 80  

      FOR XML PATH(''),TYPE)
  .value('text()[1]','nvarchar(max)'),1,2,N'')
)

EXECUTE MASTER.DBO.sp_executesql @SQL

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.