vwIndexDetail

This is an updated version of what was previously vwIndexRaw.

Creates a view that shows all indexes for all objects in all databases on this SQL instance.  System databases and objects are excluded by default, but you can optionally include them by setting @ShowSysObjects = 1.  This is helpful in ferreting out exactly what covering indexes exist for a given query, or for identifying duplicate indexes. 

IF Object_ID('dbo.CreateVwIndexDetail') Is Not Null

       DROP PROC dbo.CreateVwIndexDetail

GO

CREATE PROC dbo.CreateVwIndexDetail

      @ShowSql bit = 0,

      @ShowSysObjects bit = 0

AS

/*     DATE        AUTHOR            REMARKS

       1/3/17      PPaiva            Intial creation.

       DESCRIPTION

              Creates a view in the current database to see

                           all indexes for all objects in all databases

                           on this server.

              System objects are excluded by default;

                           use @ShowSysObjects = 1 to show them.

              Coded to use Convert(varchar, ServerProperty('ServerName'))

                           rather than @@ServerName since the latter can be erroneous.

       USAGE

              Exec CreateVwIndexDetail 1

              Exec CreateVwIndexDetail 1, 1

       DEBUG

              SELECT *

              FROM vwIndexDetail

              ORDER BY Instance, DB, SchemaName, ObjName, IndexName, Ord

              SELECT *

              FROM vwIndexDetail

              WHERE Include = 1

              ORDER BY DB, ObjName, IndexName, Ord

              SELECT *

              FROM sys.databases

*/

SET NOCOUNT ON

DECLARE @s varchar(max),

            @DB varchar(100),

            @MaxDB varchar(100),

            @i int,

            @sNow varchar(16)

SET @sNow = Convert(varchar(16), GetDate(), 120)

SET @s = 'IF Object_ID(''dbo.vwIndexDetail'') Is Not Null

      DROP VIEW dbo.vwIndexDetail'

IF @ShowSql = 1

      Print @s

Exec(@s)

    

Print ''

SET @s = 'CREATE VIEW vwIndexDetail

AS

/*    DATE        AUTHOR            REMARKS

      ' + Convert(varchar(10), GetDate(), 01) + '     PPaiva            Initial creation.

      DESCRIPTION

            Provides a list of all indexes in all databases

                  on this instance.

            This view is auto-generated on a daily basis via

                  Admin.dbo.CreateVwIndex.

            If a new database is added this view won''t show it unless

                  you run the refresh code below.

            If a database is deleted this view will malfunction unless

                  you run the refresh code below.

                

      To REFRESH VIEW when new databases are added:

            Exec Admin.dbo.CreateVwIndex

          

      -- Sample of view     

      SELECT TOP 100 *

      FROM Admin.dbo.vwIndexDetail

      ORDER BY Instance, dB, SchemaName, ObjName, IndexName, Ord

*/

'

-- Will refer to these system databases more than once,

-- so load into table for easy reference.

CREATE TABLE #SysDB(

      Name varchar(30) NOT NULL

      )

INSERT INTO #SysDB VALUES ('master')

INSERT INTO #SysDB VALUES ('tempdb')

INSERT INTO #SysDB VALUES ('model')

INSERT INTO #SysDB VALUES ('msdb')

INSERT INTO #SysDB VALUES ('Admin')

INSERT INTO #SysDB VALUES ('distribution')

SELECT name

INTO #DBs

FROM sys.databases

WHERE state_desc = 'online'

IF @ShowSysObjects = 0

      DELETE #DBs

      FROM #DBs d

      JOIN #SysDB s

            ON s.Name = d.name

SELECT  @MaxDB = Max(Name),

            @DB = '',

            @i = 0

FROM #DBs

DECLARE @IsSystemDB char(1)

WHILE @DB < @MaxDB

      BEGIN

            SET @i = @i + 1

            SELECT @DB = Min(Name)

            FROM #DBs

            WHERE Name > @DB

          

            IF @DB In (SELECT Name FROM #SysDB)

                  SET @IsSystemDB = '1'

            ELSE

                  SET @IsSystemDB = '0'

            SET @s = @s + '

SELECT  '

            SET @s = @s + 'Convert(varchar, ServerProperty(''ServerName'')) as Instance,

            ''' + @DB + ''' as DB,

            s.name COLLATE SQL_Latin1_General_CP1_CI_AS SchemaName,

            o.name COLLATE SQL_Latin1_General_CP1_CI_AS ObjName,

            i.is_primary_key PK,

            i.is_unique Uniq,

            i.name COLLATE SQL_Latin1_General_CP1_CI_AS IndexName,

            ic.key_ordinal Ord,

            c.name COLLATE SQL_Latin1_General_CP1_CI_AS ColName,

            CASE WHEN ic.is_descending_key = 0

                        THEN ''asc''

                        ELSE ''desc''

                  END Sort,

            i.type_desc Type,

            ic.is_included_column Include,

            i.is_unique_constraint UniqCons,

            i.fill_factor FF,

            ic.column_id ColID,

            i.object_id,

            c.column_id,

            o.create_date ObjCreateDate,

            o.modify_date ObjModifyDate,

            ' + @IsSystemDB + ' IsSystemDB,

            ''' + @sNow + ''' ViewCreateDate

FROM [' + @DB + '].sys.indexes i

JOIN [' + @DB + '].sys.index_columns ic

      ON  ic.object_id = i.object_id

      AND ic.index_id = i.index_id

JOIN [' + @DB + '].sys.columns c

      ON  c.object_id = ic.object_id

      AND c.column_id = ic.column_id

JOIN [' + @DB + '].sys.objects o

      ON o.object_id = i.object_id

JOIN [' + @DB + '].sys.schemas s

      ON s.schema_id = o.schema_id

'

IF @ShowSysObjects = 0

      SET @s = @s + 'WHERE o.type <> ''s''

  AND s.name <> ''sys''

'

                                  

            IF @DB <> @MaxDB

                  SET @s = @s + '    UNION ALL '

                

      END

IF @ShowSql = 1

      BEGIN

            Print @s

      END

Exec (@s)

go