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.
There is no system procedure that shows any included columns for an index. Note results of this system proc:
sp_helpindex employees
Info about included columns must be obtained from querying these two system views:
SELECT *
FROM sys.indexes
SELECT *
FROM sys.index_columns
After running CreateVwIndexRaw, this query will now show the included columns, for all objects in all databases in this SQL instance.
SELECT *
FROM Admin..vwIndexRaw
WHERE DB = 'Sopa'
AND ObjName = 'Employees'
Use Admin
go
IF object_id('dbo.CreateVwIndexRaw') Is Not Null
DROP Proc dbo.CreateVwIndexRaw
go
CREATE PROC dbo.CreateVwIndexRaw
@ShowSql bit = 0,
@ShowSysObjects bit = 0
AS
/* DATE AUTHOR REMARKS
9/18/11 PPaiva Intial creation.
DESCRIPTION
Creates a view in the Admin 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 Admin..CreateVwIndexRaw 1
Exec Admin..CreateVwIndexRaw 1, 1
DEBUG
SELECT *
FROM Admin.dbo.vwIndexRaw
ORDER BY Server, DB, SchemaName, ObjName, IndexName, Ord
SELECT *
FROM Admin.dbo.vwIndexRaw
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.vwIndexRaw'') Is Not Null
DROP VIEW dbo.vwIndexRaw'
IF @ShowSql = 1
Print @s
Exec(@s)
Print ''
SET @s = 'CREATE VIEW vwIndexRaw
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.vwIndexRaw
ORDER BY Server, 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 Server,
''' + @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)
