列出所有索引

Question

我想知道列出数据库中所有表的所有索引的最简单方法是什么。

我应该为每个表调用 sp_helpindex 并将结果存储在临时表中，还是有更简单的方法？

谁能解释为什么约束存储在 sysobjects 中而索引不存储？

Answer 1

下面是您需要的查询类型的示例：

select 
    i.name as IndexName, 
    o.name as TableName, 
    ic.key_ordinal as ColumnOrder,
    ic.is_included_column as IsIncluded, 
    co.[name] as ColumnName
from sys.indexes i 
join sys.objects o on i.object_id = o.object_id
join sys.index_columns ic on ic.object_id = i.object_id 
    and ic.index_id = i.index_id
join sys.columns co on co.object_id = i.object_id 
    and co.column_id = ic.column_id
where i.[type] = 2 
and i.is_unique = 0 
and i.is_primary_key = 0
and o.[type] = 'U'
--and ic.is_included_column = 0
order by o.[name], i.[name], ic.is_included_column, ic.key_ordinal
;

这个查询在某种程度上特定于特定目的（我在一个小型 C# 应用程序中使用它来查找重复索引并格式化输出，以便人类实际上可以读取）。 但您可以轻松地根据您的需求进行调整。

Answer 2

您可以参考 sysindexes

另一个技巧是查看 sp_helpindex 的文本了解它如何从基础表中重建信息。

sp_helptext 'sp_helpindex'

我没有这方面的参考资料，但我相信约束不会存储在 sysobjects 中，因为它们是不同类型的东西； sysindexes 包含有关 sysobjects 中对象的元数据。

Answer 3

如果您需要更多信息，这里有一个很好的 SQL 脚本，我时常使用它：

DECLARE @TabName varchar(100)

CREATE TABLE #temp (
   TabName varchar(200), IndexName varchar(200), IndexDescr varchar(200), 
   IndexKeys varchar(200), IndexSize int
)

DECLARE cur CURSOR FAST_FORWARD LOCAL FOR
    SELECT name FROM sysobjects WHERE xtype = 'U'

OPEN cur

FETCH NEXT FROM cur INTO @TabName
WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT INTO #temp (IndexName, IndexDescr, IndexKeys)
        EXEC sp_helpindex @TabName

        UPDATE #temp SET TabName = @TabName WHERE TabName IS NULL

        FETCH NEXT FROM cur INTO @TabName
    END

CLOSE cur
DEALLOCATE cur

DECLARE @ValueCoef int
SELECT @ValueCoef = low FROM Master.dbo.spt_values WHERE number = 1 AND type = N'E'

UPDATE #temp SET IndexSize = 
    ((CAST(sysindexes.used AS bigint) * @ValueCoef)/1024)/1024
        FROM sysobjects INNER JOIN sysindexes ON sysobjects.id = sysindexes.id
            INNER JOIN #temp T ON T.TabName = sysobjects.name AND T.IndexName = sysindexes.name

SELECT * FROM #temp
ORDER BY TabName, IndexName 

DROP TABLE #temp

Answer 4

下面是一个脚本，它将返回 SQL 语句以重新创建数据库中的所有索引。

SELECT ' CREATE ' +
       CASE 
            WHEN I.is_unique = 1 THEN ' UNIQUE '
            ELSE ''
       END +
       I.type_desc COLLATE DATABASE_DEFAULT + ' INDEX ' +
       I.name + ' ON ' +
       SCHEMA_NAME(T.schema_id) + '.' + T.name + ' ( ' +
       KeyColumns + ' )  ' +
       ISNULL(' INCLUDE (' + IncludedColumns + ' ) ', '') +
       ISNULL(' WHERE  ' + I.filter_definition, '') + ' WITH ( ' +
       CASE 
            WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON '
            ELSE ' PAD_INDEX = OFF '
       END + ',' +
       'FILLFACTOR = ' + CONVERT(
           CHAR(5),
           CASE 
                WHEN I.fill_factor = 0 THEN 100
                ELSE I.fill_factor
           END
       ) + ',' +
       -- default value 
       'SORT_IN_TEMPDB = OFF ' + ',' +
       CASE 
            WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON '
            ELSE ' IGNORE_DUP_KEY = OFF '
       END + ',' +
       CASE 
            WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF '
            ELSE ' STATISTICS_NORECOMPUTE = ON '
       END + ',' +
       ' ONLINE = OFF ' + ',' +
       CASE 
            WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON '
            ELSE ' ALLOW_ROW_LOCKS = OFF '
       END + ',' +
       CASE 
            WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON '
            ELSE ' ALLOW_PAGE_LOCKS = OFF '
       END + ' ) ON [' +
       DS.name + ' ] ' +  CHAR(13) + CHAR(10) + ' GO' [CreateIndexScript]
FROM   sys.indexes I
       JOIN sys.tables T
            ON  T.object_id = I.object_id
       JOIN sys.sysindexes SI
            ON  I.object_id = SI.id
            AND I.index_id = SI.indid
       JOIN (
                SELECT *
                FROM   (
                           SELECT IC2.object_id,
                                  IC2.index_id,
                                  STUFF(
                                      (
                                          SELECT ' , ' + C.name + CASE 
                                                                       WHEN MAX(CONVERT(INT, IC1.is_descending_key)) 
                                                                            = 1 THEN 
                                                                            ' DESC '
                                                                       ELSE 
                                                                            ' ASC '
                                                                  END
                                          FROM   sys.index_columns IC1
                                                 JOIN sys.columns C
                                                      ON  C.object_id = IC1.object_id
                                                      AND C.column_id = IC1.column_id
                                                      AND IC1.is_included_column = 
                                                          0
                                          WHERE  IC1.object_id = IC2.object_id
                                                 AND IC1.index_id = IC2.index_id
                                          GROUP BY
                                                 IC1.object_id,
                                                 C.name,
                                                 index_id
                                          ORDER BY
                                                 MAX(IC1.key_ordinal) 
                                                 FOR XML PATH('')
                                      ),
                                      1,
                                      2,
                                      ''
                                  ) KeyColumns
                           FROM   sys.index_columns IC2 
                                  --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
                           GROUP BY
                                  IC2.object_id,
                                  IC2.index_id
                       ) tmp3
            )tmp4
            ON  I.object_id = tmp4.object_id
            AND I.Index_id = tmp4.index_id
       JOIN sys.stats ST
            ON  ST.object_id = I.object_id
            AND ST.stats_id = I.index_id
       JOIN sys.data_spaces DS
            ON  I.data_space_id = DS.data_space_id
       JOIN sys.filegroups FG
            ON  I.data_space_id = FG.data_space_id
       LEFT JOIN (
                SELECT *
                FROM   (
                           SELECT IC2.object_id,
                                  IC2.index_id,
                                  STUFF(
                                      (
                                          SELECT ' , ' + C.name
                                          FROM   sys.index_columns IC1
                                                 JOIN sys.columns C
                                                      ON  C.object_id = IC1.object_id
                                                      AND C.column_id = IC1.column_id
                                                      AND IC1.is_included_column = 
                                                          1
                                          WHERE  IC1.object_id = IC2.object_id
                                                 AND IC1.index_id = IC2.index_id
                                          GROUP BY
                                                 IC1.object_id,
                                                 C.name,
                                                 index_id 
                                                 FOR XML PATH('')
                                      ),
                                      1,
                                      2,
                                      ''
                                  ) IncludedColumns
                           FROM   sys.index_columns IC2 
                                  --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
                           GROUP BY
                                  IC2.object_id,
                                  IC2.index_id
                       ) tmp1
                WHERE  IncludedColumns IS NOT NULL
            ) tmp2
            ON  tmp2.object_id = I.object_id
            AND tmp2.index_id = I.index_id
WHERE  I.is_primary_key = 0
       AND I.is_unique_constraint = 0
       --AND T.name NOT LIKE 'mt_%'
       --AND I.name NOT LIKE 'mt_%'
           --AND I.Object_id = object_id('Person.Address') --Comment for all tables
           --AND I.name = 'IX_Address_PostalCode' --comment for all indexes 

Answer 5

我没有明确解释为什么索引不存储在 sys.objects 中。 但我想帮助找到一种简单的方法来列出数据库中所有表和视图的所有索引。 以下查询检索所有索引，包括它们的类型以及它们的对象 ID 和对象类型。

use /*Enter here your database*/
go
select A.Object_id,B.name,B.type,B.type_desc, A.index_id,A.type,A.type_desc
from sys.indexes A left join sys.objects B on A.object_id=B.object_id
where B.type = 'U' or B.type='V' /*filtering on U or V to retrieve tables and views only*/
order by B.name ASC /*Optional sorting*/

Answer 6

我编写了这段代码来迭代服务器中的所有数据库，并将其推送到名为 Maintenance 的数据库中的表中。 您应该首先创建此数据库，然后在该数据库中创建一个包含以下字段的表：

CREATE TABLE [dbo].[DBCC_Stats](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [DatabaseName] [varchar](50) NULL,
    [SchemaName] [nvarchar](128) NULL,
    [TableName] [sysname] NOT NULL,
    [StatName] [nvarchar](128) NULL,
    [modification_counter] [bigint] NULL,
    [rows] [bigint] NULL,
    [rows_sampled] [bigint] NULL,
    [% Rows Sampled] [bigint] NULL,
    [last_updated] [datetime2](7) NULL,
    [DateEntered] [datetime] NULL,
 CONSTRAINT [PK_DBCC_Stats] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[DBCC_Stats] ADD  CONSTRAINT [DF_DBCC_Stats_DateEntered]  DEFAULT (getdate()) FOR [DateEntered]

要使用下面的存储过程，您需要传入服务器名称。
usp_Execute_Stats '[您的服务器名称]'

`CREATE PROCEDURE usp_Execute_Stats
    @ServerName varchar(100)
AS
BEGIN
DECLARE @strSQL varchar(max)
SET @strSQL='USE ?
SELECT      ''' + '?' + ''' AS DatabaseName,OBJECT_SCHEMA_NAME(obj.object_id) SchemaName, obj.name TableName,
           stat.name StatName, modification_counter,
            [rows], rows_sampled, rows_sampled* 100 / [rows] AS [% Rows Sampled],
            last_updated
FROM        ' + @ServerName + '.' + '?' + '.sys.objects AS obj
INNER JOIN  ' + @ServerName + '.' + '?' + '.sys.stats AS stat ON stat.object_id = obj.object_id
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE       obj.is_ms_shipped = 0
ORDER BY    modification_counter DESC'
INSERT INTO Maintenance.dbo.vwDBCC_Stats
EXEC sp_MSforeachdb @strSQL
--Delete older logs
DELETE Maintenance.dbo.DBCC_Stats
    --WHERE DatabaseName IN('Master','Model','MSDB','TempDB')
    WHERE [DateEntered] < getdate()-14
END`