截圖/110408_0836_1.png)
對(duì)取包含字段時(shí)用到了FOR XML PATH這個(gè)功能，可以方便的把包含列組織成A,B,C的形式。然后使用CROSS APPLY得出最終的結(jié)果。腳本定義如下：
USE AdventureWorks;
GO
DROP INDEX IX_SalesOrderHeader_CustomerID ON Sales.SalesOrderHeader
GO
CREATE INDEX IX_SalesOrderHeader_CustomerID ON Sales.SalesOrderHeader(CustomerID)
INCLUDE(ShipDate,Freight)
GO
--sp_helpindex不能反應(yīng)出包含字段
EXEC sp_helpindex 'Sales.SalesOrderHeader'
GO
--SQL2005下用于診斷索引重復(fù)的腳本
DECLARE @Result TABLE(
objname sysname NOT NULL,
objtype char(2) NOT NULL,
indexname sysname NOT NULL,
index_id int NOT NULL,
indextype tinyint NOT NULL,
is_primary_key bit NOT NULL,
is_unique bit NOT NULL,
fill_factor tinyint NOT NULL,
IndexKeys nvarchar(2126) NOT NULL,
Included nvarchar(max) NULL,
rows bigint NOT NULL,
IndexDesc varchar(210) NULL
)
CREATE TABLE #IndexInfo
(
IndexName sysname NOT NULL,
IndexDesc varchar(210) NULL,
IndexKeys nvarchar(2126) NULL
)
DECLARE @objname sysname
DECLARE ObjectList CURSOR FAST_FORWARD FOR
SELECT SCHEMA_NAME(o.schema_id)+'.'+o.name AS objname
FROM sys.indexes i JOIN sys.objects o ON i.object_id=o.object_id
WHERE o.type IN('U','V') AND i.index_id IN(0,1)
--AND o.object_id=OBJECT_ID(N'Sales.SalesOrderHeader')
OPEN ObjectList
FETCH NEXT FROM ObjectList INTO @objname
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #IndexInfo EXEC sp_helpindex @objname--使用全名稱(chēng)，防止直接使用表名稱(chēng)時(shí)無(wú)法獲取其它架構(gòu)表的信息
INSERT INTO @Result
SELECT SCHEMA_NAME(o.schema_id)+'.'+o.name AS objname, o.type AS objtype,
i.name AS indexname,i.index_id,i.type AS indextype,i.is_primary_key,i.is_unique,i.fill_factor,
t.IndexKeys,
c.name AS Included,
p.rows,t.IndexDesc
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id=o.object_id
INNER JOIN #IndexInfo t ON t.IndexName=i.name
CROSS APPLY (SELECT SUM(rows) AS rows
FROM sys.partitions p
WHERE p.index_id = i.index_id AND p.object_id = i.object_id
) p
CROSS APPLY (SELECT name=STUFF((SELECT N',' + QUOTENAME(y) AS [text()]
FROM (SELECT c.name AS y
FROM sys.index_columns ic
JOIN sys.columns c ON ic.column_id=c.column_id AND ic.object_id=c.object_id
WHERE ic.object_id=i.object_id AND ic.index_id=i.index_id AND ic.is_included_column=1
) AS Y
ORDER BY y FOR XML PATH('')), 1, 1, N'')
) c
WHERE o.object_id=OBJECT_ID(@objname)
TRUNCATE TABLE #IndexInfo
FETCH NEXT FROM ObjectList INTO @objname
END
CLOSE ObjectList
DEALLOCATE ObjectList
DROP TABLE #IndexInfo
SELECT * FROM @Result ORDER BY objname,index_id
用于SQL2000的腳本：
--SQL2000下用于診斷索引重復(fù)的腳本
DECLARE @Result TABLE (
[objname] [sysname] NOT NULL ,
[indexname] [sysname] NOT NULL ,
[indid] [smallint] NOT NULL ,
[IsUnique] [int] NOT NULL ,
[IndexKeys] [nvarchar] (2126) NOT NULL ,
[rowcnt] [bigint] NOT NULL ,
[rowmodctr] [int] NOT NULL ,
[keycnt] [smallint] NOT NULL ,
[OrigFillFactor] [tinyint] NOT NULL ,
[dpages] [int] NOT NULL ,
[IndexDesc] [varchar] (210) NULL
)
CREATE TABLE #IndexInfo
(
IndexName sysname NOT NULL,
IndexDesc varchar(210) NULL,
IndexKeys nvarchar(2126) NULL
)
DECLARE @objname sysname,
@objid int
DECLARE ObjectList CURSOR FAST_FORWARD FOR
SELECT USER_NAME(o.uid)+'.'+o.name AS objname,o.id AS objid
FROM dbo.sysobjects o JOIN dbo.sysindexes i ON i.id = o.id
WHERE o.type IN( 'U','V') AND i.indid IN(0,1) AND o.name<>'dtproperties'--用于保存關(guān)系圖的系統(tǒng)表
ORDER BY o.name,o.uid
OPEN ObjectList
FETCH NEXT FROM ObjectList INTO @objname,@objid
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #IndexInfo EXEC sp_helpindex @objname--使用全名稱(chēng)，防止直接使用表名稱(chēng)時(shí)無(wú)法獲取其它用戶(hù)表的信息
INSERT INTO @Result
SELECT USER_NAME(o.uid)+'.'+o.name AS objname, i.name AS indexname, i.indid,
CASE WHEN t.IndexDesc LIKE '%unique%' THEN 1 ELSE 0 END AS IsUnique,
t.IndexKeys, i.rowcnt, i.rowmodctr, i.keycnt, i.OrigFillFactor, i.dpages,t.IndexDesc
FROM dbo.sysindexes i
INNER JOIN dbo.sysobjects o ON i.id = o.id
INNER JOIN #IndexInfo t ON t.IndexName=i.name
WHERE o.id=@objid
TRUNCATE TABLE #IndexInfo
FETCH NEXT FROM ObjectList INTO @objname,@objid
END
CLOSE ObjectList
DEALLOCATE ObjectList
DROP TABLE #IndexInfo
SELECT * FROM @Result ORDER BY objname,indid