USE master
--DROP PROCEDURE sp_indexinfoAllDBSub
GO
CREATE OR ALTER PROCEDURE sp_indexinfoAllDBSub  @missing_ix TINYINT = 1
AS BEGIN
--Written by Tibor Karaszi 2008-07-07 
--Last modified by Tibor Karaszi 2010-05-24
--modified by Henrik Staun Poulsen 2017-12-13
  SET NOCOUNT ON 
  DROP TABLE IF EXISTS #Result
  CREATE TABLE #Result (DatabaseName sysname, Schema_name sysname, Table_name sysname, Index_name VARCHAR(1000), IdxNo INT, type VARCHAR(10), Depth INT, Notes VARCHAR(20), ISUnique tinyint, cnstr VARCHAR(10), key_columns VARCHAR(max)
  , included_columns VARCHAR(max), Filtered VARCHAR(max), Location VARCHAR(500), rows BIGINT, pages BIGINT, MB BIGINT, user_seeks BIGINT, user_scans BIGINT, user_lookups BIGINT, user_updates BIGINT, Seeks BIGINT, Scans BIGINT, Lookups BIGINT, Updates BIGINT, Partition_columns VARCHAR(100)
  )

  DROP TABLE IF EXISTS #MissingIndex
  CREATE TABLE #MissingIndex (DatabaseName sysname, Schema_name sysname, Table_name sysname, IndexDDL VARCHAR(MAX), user_seeks BIGINT, user_scans BIGINT, avg_user_impact DECIMAL(9,2))

  DECLARE @sql NVARCHAR(max) 

  /* declare variables */
  DECLARE @variable sysname

  DECLARE dbcursor CURSOR FAST_FORWARD READ_ONLY FOR
  SELECT name FROM sys.databases WHERE state_desc='online'
  AND name NOT IN ('tempdb', 'master', 'msdb', 'model')

  OPEN dbcursor

  FETCH NEXT FROM dbcursor INTO @variable

  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @sql = N'USE [<database>];
      ;
      WITH key_columns AS 
      ( 
      SELECT 
       c.OBJECT_ID 
      ,c.name AS column_name 
      ,ic.key_ordinal 
      ,ic.is_included_column 
      ,ic.index_id 
      ,ic.is_descending_key 
      , ic.partition_ordinal
      FROM sys.columns AS c WITH (NOLOCK)
       INNER JOIN sys.index_columns AS ic WITH (NOLOCK) ON c.OBJECT_ID = ic.OBJECT_ID AND ic.column_id = c.column_id 
      ) 
      , physical_info AS 
      ( 
      SELECT p.OBJECT_ID, p.index_id, ds.name AS location, SUM(p.rows) AS rows, SUM(a.total_pages) AS pages 
      FROM sys.partitions AS p WITH (NOLOCK) 
      INNER JOIN sys.allocation_units AS a WITH (NOLOCK) ON p.hobt_id = a.container_id 
      INNER JOIN sys.data_spaces AS ds WITH (NOLOCK) ON a.data_space_id = ds.data_space_id 
      GROUP BY OBJECT_ID, index_id, ds.name 
      ) 
      insert into #result
      SELECT 
      ''<database>'' as db,
       OBJECT_SCHEMA_NAME(i.OBJECT_ID) AS schema_name 
      ,OBJECT_NAME(i.OBJECT_ID) AS table_name 
      , coalesce(i.name, ''HEAP'') AS index_name 
      ,i.index_id AS IdxNo
      ,CASE i.type 
        WHEN 0 THEN ''heap'' 
        WHEN 1 THEN ''cl'' 
        WHEN 2 THEN ''nc'' 
        WHEN 3 THEN ''xml'' 
        ELSE CAST(i.type AS VARCHAR(2)) 
       END 
       AS type 
      , INDEXPROPERTY(i.object_id, i.name, ''IndexDepth'') as Depth   
      , CONCAT(  CASE WHEN INDEXPROPERTY(i.object_id, i.name, ''IsDisabled'') <>0 THEN ''IX Disabled! '' END,
        CASE WHEN INDEXPROPERTY(i.object_id,
       i.name, ''IndexFillFactor '') BETWEEN 1 AND 99 THEN ''Fillfactor=''+LTRIM(INDEXPROPERTY(i.object_id,
       i.name, ''IndexFillFactor''))+'' '' END,
        CASE WHEN INDEXPROPERTY(i.object_id,
       i.name,
       ''IsPageLockDisallowed'')<>0 THEN ''PageLock is Disallowed '' END,
        CASE WHEN INDEXPROPERTY(i.object_id,
       i.name,
       ''IsRowLockDisallowed'')<>0 THEN ''RowLock is Disallowed'' END 
      ) AS Notes
      ,i.is_unique AS [Unique]
      ,CASE 
        WHEN is_primary_key = 0 AND is_unique_constraint = 0 THEN ''no'' 
        WHEN is_primary_key = 1 AND is_unique_constraint = 0 THEN ''PK'' 
        WHEN is_primary_key = 0 AND is_unique_constraint = 1 THEN ''UQ'' 
       END 
       AS cnstr 
      , REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(STUFF((SELECT CAST('', '' + kc.column_name + CASE kc.is_descending_key WHEN 0 THEN '''' ELSE '' DESC'' END AS VARCHAR(MAX)) 
        AS [text()]  
        FROM key_columns AS kc WITH (NOLOCK) 
        WHERE i.OBJECT_ID = kc.OBJECT_ID AND i.index_id = kc.index_id AND kc.is_included_column = 0 AND kc.key_ordinal<>0 -- AND kc.partition_ordinal=0
        ORDER BY key_ordinal  
        FOR XML PATH('''')  
        ),  1, 2, ''''), CHAR(13), ''''), ''&'', ''''), CHAR(10), ''''), '';'', ''''), ''#x0D'', '''') AS key_columns 

      , REPLACE(REPLACE(CASE WHEN i.type =1 THEN ''(All columns)'' 
        ELSE COALESCE(STUFF((SELECT CAST('', '' + column_name AS VARCHAR(MAX)) AS [text()] 
        FROM key_columns AS kc WITH (NOLOCK) 
        WHERE i.OBJECT_ID = kc.OBJECT_ID AND i.index_id = kc.index_id AND kc.is_included_column = 1 
        ORDER BY key_ordinal 
        FOR XML PATH('''')  ), 1, 2, ''''),'''') END, CHAR(13), ''''), CHAR(10), '''') AS included_columns 
      ,
      COALESCE(i.filter_definition,
      '''') AS [Filtered?] 
      , CASE WHEN i.is_disabled = 1 THEN ''Disabled'' ELSE p.location  END AS Location
      , p.rows 
      , p.pages 
      , CAST((p.pages * 8.00) / 1024 AS decimal(9,0)) AS MB 
      , coalesce(s.user_seeks  , 0)
      , coalesce(s.user_scans  , 0)
      , coalesce(s.user_lookups, 0) 
      , coalesce(s.user_updates, 0) 
      , coalesce(s.system_seeks + s.user_seeks, 0)        AS Seeks 
      , coalesce(s.system_scans + s.user_scans, 0)  		  AS Scans 
      , coalesce(s.system_lookups + s.user_lookups, 0)	  AS Lookups 
      , coalesce(s.system_updates + s.user_updates, 0)	  AS Updates 
      , COALESCE(STUFF((SELECT CAST('', '' + kc.column_name + CASE kc.is_descending_key WHEN 0 THEN ''''  ELSE '' DESC''  END AS VARCHAR(MAX)) AS [text()]  
      FROM key_columns AS kc WITH (NOLOCK) 
      WHERE i.OBJECT_ID = kc.OBJECT_ID AND i.index_id = kc.index_id AND kc.is_included_column = 0 AND kc.partition_ordinal=1
      ORDER BY key_ordinal  
      FOR XML PATH('''')  
       ), 1, 2, ''''),'''') AS Partition_columns 
      FROM sys.indexes AS i WITH (NOLOCK) 
      LEFT OUTER JOIN physical_info AS p WITH (NOLOCK)  ON i.OBJECT_ID = p.OBJECT_ID AND i.index_id = p.index_id 
      LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s WITH (NOLOCK)  ON s.OBJECT_ID = i.OBJECT_ID AND s.index_id = i.index_id AND s.database_id = DB_ID() 
      WHERE OBJECTPROPERTY(i.OBJECT_ID, ''IsMsShipped'') = 0  
      AND OBJECTPROPERTY(i.OBJECT_ID, ''IsTableFunction'') = 0 
      ORDER BY OBJECT_SCHEMA_NAME(i.OBJECT_ID)--, table_name, index_name  

    '

    SELECT @sql=REPLACE(@sql, '<database>', @variable)
    PRINT @variable
    --SELECT @sql
    EXEC sp_executesql @sql


    
  IF @missing_ix = 1 BEGIN 
    SELECT @sql='
    USE [<database>];
    insert into #MissingIndex
    SELECT 
      ''<database>'' as db,
     OBJECT_SCHEMA_NAME(d.OBJECT_ID) AS schema_name   
    , OBJECT_NAME(d.OBJECT_ID) AS table_name   
    , ''CREATE INDEX IX_'' + REPLACE(REPLACE(REPLACE(COALESCE(d.equality_columns + COALESCE('', '' + d.inequality_columns, ''''), d.inequality_columns, ''IndexName''),''['',''''),'']'',''''),'', '',''_'') + '' ON '' + OBJECT_SCHEMA_NAME(d.OBJECT_ID) + ''.'' + OBJECT_NAME(d.OBJECT_ID) + '' ''   + ''('' + COALESCE(d.equality_columns + COALESCE('', '' + d.inequality_columns,
     ''''), d.inequality_columns) + '')''    + COALESCE('' INCLUDE('' + d.included_columns + '')'', '''')  AS ddl 
    ,s.user_seeks 
    ,s.user_scans 
    ,s.avg_user_impact 
    FROM sys.dm_db_missing_index_details AS d WITH (NOLOCK) 
     INNER JOIN  sys.dm_db_missing_index_groups AS g WITH (NOLOCK) 
      ON d.index_handle = g.index_handle 
     INNER JOIN sys.dm_db_missing_index_group_stats AS s WITH (NOLOCK) 
      ON g.index_group_handle = s.group_handle 
    WHERE d.database_id = DB_ID() 
    ORDER BY avg_user_impact DESC '
    SELECT @sql=REPLACE(@sql, '<database>', @variable)
    PRINT @variable
    --SELECT @sql
    EXEC sp_executesql @sql
  END


  
      FETCH NEXT FROM dbcursor INTO @variable
  END

  CLOSE dbcursor
  DEALLOCATE dbcursor

  SELECT DatabaseName AS DatabaseName, Schema_name, Table_name, Index_name, IdxNo, type, Depth, Notes, ISUnique, cnstr, key_columns, included_columns, Filtered, Location
  , FORMAT(rows, 'N0') AS Rows, FORMAT(pages, 'N0') AS Pages, FORMAT(MB, 'N0') AS MB
  , FORMAT(user_seeks, 'N0') AS User_Seeks, FORMAT(user_scans, 'N0') AS User_Scans, FORMAT(user_lookups, 'N0') AS User_Lookups
  , FORMAT(user_updates, 'N0') AS User_Updates, FORMAT(Seeks, 'N0') AS Seeks, FORMAT(Scans, 'N0') AS Scans, FORMAT(Lookups, 'N0') AS Lookups
  , FORMAT(Updates, 'N0') AS Updates, Partition_columns 
  FROM #Result
  WHERE table_name NOT IN ('__MigrationHistory')
  ORDER BY DatabaseName, schema_name, table_name

  IF @missing_ix = 1 BEGIN 
    SELECT top 10000 * FROM #MissingIndex
    ORDER BY DatabaseName, schema_name, table_name
  END 


END
GO
EXEC sp_indexinfoAllDBSub