Sql-server – List of all database without a specific index

indexsql serversql-server-2012

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

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

Best Answer

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).

Related Solutions

Sql-server – List all database containing table and value

I'm guessing on the first column - replace ColumnName below with the actual column name. Also it's always useful to specify which version of SQL Server you are using - I've assumed 2005.

DECLARE @DBType CHAR(1);
SET @DBType = 'A'; -- or 'B';

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

CREATE TABLE #x(DB SYSNAME);

SELECT @sql = @sql + 'IF EXISTS (SELECT 1 FROM '
  + QUOTENAME(name) + '.sys.tables WHERE name = ''DBType'')
  INSERT #x SELECT ''' + name + '''
    FROM ' + QUOTENAME(name) + '.dbo.DBType
    WHERE ColumnName = ''' + @DBType + ''';'
FROM sys.databases;

EXEC sp_executesql @sql;

SELECT DB FROM #x;

DROP TABLE #x;

Sql-server – Find the disk size of specific rows

One way is to look at the sys.dm_db_index_physical_stats output. The min_record_size_in_bytes, max_record_size_in_bytes and avg_record_size_in_bytes will give you the sizes you want.

If you want to see the size of a particular record, another way is to find the physical row location, see SQL Server 2008: New (undocumented) physical row locator function. You can then dump the row physical structure, see Anatomy of a Record.

Finally you can use the product documentation to estimate the size:

Best Answer

Related Solutions

Sql-server – List all database containing table and value

Sql-server – Find the disk size of specific rows

Related Question