보미네

Partition

PartitionScheme (function name)

PartitionCount 

FileGroup

Schema Binding indexed View

Include 

ColumnStore 

Unique

UniqueConstraint

Primarykey

Filtered Index Definition

Disabled

IndexSize

index stat 

physical stat

statistics update date

쓰는 인덱스 인지 안 쓰는 인덱스 인지 뭐 정보가 좀 필요할듯 해 만들었습니다. 

치훈이가 이름 붙여줌...

믿고쓰는 김장애 선생님의 sp_helpindex2 

장애나면 연락 하세요~ ^.^~

-- 자 갑니다. ~

STAT3 은 주석 처리 하세요~ 

용량 큰 테이블이 있으면 오래 걸립니다. ~

sql 2014

IF OBJECT_ID('SP_HELPINDEX2') IS NULL
	EXEC ('CREATE PROC SP_HELPINDEX2 AS SELECT 1 ')
GO

ALTER PROCEDURE SP_HELPINDEX2 @NAME NVARCHAR(4000) = ''
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- SCRIPT BY MINSOUK KIM 
-- 01099670955
-- A.TO.Z@NAVERCORP.COM
SELECT ISNULL(PS.TYPE_DESC, 'NORMAL') AS ISPTN
	,PS.NAME AS PTNFUNCNAME
	,D.PARTITIONCNT AS PTNCNT
	,T1.TYPE_DESC
	,FI.NAME AS FGNAME
	,SCHEMA_NAME(SCHEMA_ID) AS SNAME
	,T1.CREATE_DATE AS TBLCREDATE
	,T1.NAME AS OBJNAME
	,I.TYPE_DESC ISCLUSTERORHEAP
	,I.NAME AS IXNAME
	,INDEXSIZEKB AS IXSIZEKB
	,SUBSTRING(B.INDEX_COLUMN_DESC, 3, 1000) AS IXDESC
	,SUBSTRING(C.INCLUDE_COLUMN_DESC, 3, 1000) AS INCOLDESC
	,STATS_DATE(i.object_id, i.index_id) AS [STATISTICUPDATEDATE]
	,I.IS_UNIQUE
	,I.IS_UNIQUE_CONSTRAINT
	,I.IS_PRIMARY_KEY
	,I.HAS_FILTER
	,I.FILTER_DEFINITION
	,I.IS_DISABLED
	,STAT1.*
	,STAT2.*
--,STAT3.*
--	, * 
FROM SYS.OBJECTS AS T1
INNER JOIN SYS.INDEXES AS I ON T1.[OBJECT_ID] = I.[OBJECT_ID]
LEFT JOIN SYS.FILEGROUPS FI ON I.DATA_SPACE_ID = FI.DATA_SPACE_ID
--    AND I.[TYPE] IN (0,1) 
LEFT JOIN SYS.PARTITION_SCHEMES PS ON I.DATA_SPACE_ID = PS.DATA_SPACE_ID
OUTER APPLY (
	SELECT TOP 100 PERCENT ', ' + AC.NAME + CASE 
			WHEN IS_DESCENDING_KEY = 1
				THEN ' DESC'
			ELSE ' ASC'
			END AS [text()]
	FROM SYS.INDEX_COLUMNS IC
	INNER JOIN SYS.ALL_COLUMNS AC ON IC.OBJECT_ID = AC.OBJECT_ID
		AND IC.COLUMN_ID = AC.COLUMN_ID
	WHERE IC.OBJECT_ID = I.OBJECT_ID
		AND IC.INDEX_ID = I.INDEX_ID
		AND IC.IS_INCLUDED_COLUMN = 0
	ORDER BY IC.INDEX_COLUMN_ID
	FOR XML PATH('')
	) B(INDEX_COLUMN_DESC)
OUTER APPLY (
	SELECT TOP 100 PERCENT ', ' + AC.NAME AS [text()]
	FROM SYS.INDEX_COLUMNS IC
	INNER JOIN SYS.ALL_COLUMNS AC ON IC.OBJECT_ID = AC.OBJECT_ID
		AND IC.COLUMN_ID = AC.COLUMN_ID
	WHERE IC.OBJECT_ID = I.OBJECT_ID
		AND IC.INDEX_ID = I.INDEX_ID
		AND IC.IS_INCLUDED_COLUMN = 1
	ORDER BY IC.INDEX_COLUMN_ID
	FOR XML PATH('')
	) C(INCLUDE_COLUMN_DESC)
OUTER APPLY (
	SELECT COUNT(*)
	FROM SYS.TABLES AS T
	INNER JOIN SYS.INDEXES AS I ON T.OBJECT_ID = I.OBJECT_ID
	INNER JOIN SYS.PARTITIONS AS P ON I.OBJECT_ID = P.OBJECT_ID
		AND I.INDEX_ID = P.INDEX_ID
	INNER JOIN SYS.PARTITION_SCHEMES AS S ON I.DATA_SPACE_ID = S.DATA_SPACE_ID
	INNER JOIN SYS.PARTITION_FUNCTIONS AS F ON S.FUNCTION_ID = F.FUNCTION_ID
	LEFT JOIN SYS.PARTITION_RANGE_VALUES AS R ON F.FUNCTION_ID = R.FUNCTION_ID
		AND R.BOUNDARY_ID = P.PARTITION_NUMBER
	WHERE T.NAME = T1.NAME
		AND I.TYPE <= 1
	) D(PARTITIONCNT)
OUTER APPLY (
	SELECT 8 * SUM(A.USED_PAGES) AS 'INDEXSIZE(KB)'
	FROM SYS.INDEXES AS III
	INNER JOIN SYS.PARTITIONS AS P ON P.OBJECT_ID = III.OBJECT_ID
		AND P.INDEX_ID = I.INDEX_ID
	INNER JOIN SYS.ALLOCATION_UNITS AS A ON A.CONTAINER_ID = P.PARTITION_ID
	WHERE III.OBJECT_ID = T1.OBJECT_ID
		AND III.INDEX_ID = I.INDEX_ID
	) E(INDEXSIZEKB)
OUTER APPLY (
	SELECT TOP 1 *
	FROM SYS.DM_DB_INDEX_OPERATIONAL_STATS(DB_ID(), T1.OBJECT_ID, I.INDEX_ID, NULL)
	WHERE 1 = CASE 
			WHEN T1.TYPE_DESC = 'SQL_TABLE_VALUED_FUNCTION'
				THEN 0
			ELSE 1
			END
	) STAT2
OUTER APPLY (
	SELECT TOP 1 *
	FROM (
		SELECT *
		FROM SYS.DM_DB_INDEX_USAGE_STATS
		) A
	WHERE OBJECT_ID = T1.OBJECT_ID
		AND INDEX_ID = I.INDEX_ID
                    AND DATABASE_ID = DB_ID()
	) STAT1
OUTER APPLY (
	SELECT TOP 1 *
	FROM SYS.dm_db_index_physical_stats(DB_ID(), T1.OBJECT_ID, I.INDEX_ID, NULL, NULL)
	) STAT3
WHERE IS_MS_SHIPPED = 0
	AND T1.NAME = CASE 
		WHEN @NAME = ''
			THEN T1.NAME
		ELSE @NAME
		END
ORDER BY 1
	,2
	,3
	,4
	,5
	,6
	,7
	,I.INDEX_ID
GO

EXEC sp_helpindex2
GO

2008 r2

IF OBJECT_ID('SP_HELPINDEX2') IS NULL
	EXEC ('CREATE PROC SP_HELPINDEX2 AS SELECT 1 ')
GO

ALTER PROCEDURE SP_HELPINDEX2 @NAME NVARCHAR(4000) = ''
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- SCRIPT BY MINSOUK KIM 
-- 01099670955
-- A.TO.Z@NAVERCORP.COM
SELECT ISNULL(PS.TYPE_DESC, 'NORMAL') AS ISPTN
	, PS.NAME AS PTNFUNCNAME
	, D.PARTITIONCNT AS PTNCNT
	, T1.TYPE_DESC
	, FI.NAME AS FGNAME
	, SCHEMA_NAME(SCHEMA_ID) AS SNAME
	, T1.CREATE_DATE AS TBLCREDATE
	, T1.NAME AS OBJNAME
	, I.TYPE_DESC ISCLUSTERORHEAP
	, I.NAME AS IXNAME
	, INDEXSIZEKB AS IXSIZEKB
	, SUBSTRING(B.INDEX_COLUMN_DESC, 3, 1000) AS IXDESC
	, SUBSTRING(C.INCLUDE_COLUMN_DESC, 3, 1000) AS INCOLDESC
	, STATS_DATE(i.object_id, i.index_id) AS [STATISTICUPDATEDATE]
	, I.IS_UNIQUE
	, I.IS_UNIQUE_CONSTRAINT
	, I.IS_PRIMARY_KEY
	, I.HAS_FILTER
	, I.FILTER_DEFINITION
	, I.IS_DISABLED
	, STAT1.*
	, STAT2.*
--,STAT3.*
--	, * 
FROM SYS.OBJECTS AS T1
INNER JOIN SYS.INDEXES AS I ON T1.[OBJECT_ID] = I.[OBJECT_ID]
LEFT JOIN SYS.FILEGROUPS FI ON I.DATA_SPACE_ID = FI.DATA_SPACE_ID
--    AND I.[TYPE] IN (0,1) 
LEFT JOIN SYS.PARTITION_SCHEMES PS ON I.DATA_SPACE_ID = PS.DATA_SPACE_ID
OUTER APPLY (
	SELECT TOP 100 PERCENT ', ' + AC.NAME + CASE 
			WHEN IS_DESCENDING_KEY = 1
				THEN ' DESC'
			ELSE ' ASC'
			END AS [text()]
	FROM SYS.INDEX_COLUMNS IC
	INNER JOIN SYS.ALL_COLUMNS AC ON IC.OBJECT_ID = AC.OBJECT_ID
		AND IC.COLUMN_ID = AC.COLUMN_ID
	WHERE IC.OBJECT_ID = I.OBJECT_ID
		AND IC.INDEX_ID = I.INDEX_ID
		AND IC.IS_INCLUDED_COLUMN = 0
	ORDER BY IC.INDEX_COLUMN_ID
	FOR XML PATH('')
	) B(INDEX_COLUMN_DESC)
OUTER APPLY (
	SELECT TOP 100 PERCENT ', ' + AC.NAME AS [text()]
	FROM SYS.INDEX_COLUMNS IC
	INNER JOIN SYS.ALL_COLUMNS AC ON IC.OBJECT_ID = AC.OBJECT_ID
		AND IC.COLUMN_ID = AC.COLUMN_ID
	WHERE IC.OBJECT_ID = I.OBJECT_ID
		AND IC.INDEX_ID = I.INDEX_ID
		AND IC.IS_INCLUDED_COLUMN = 1
	ORDER BY IC.INDEX_COLUMN_ID
	FOR XML PATH('')
	) C(INCLUDE_COLUMN_DESC)
OUTER APPLY (
	SELECT COUNT(*)
	FROM SYS.TABLES AS T
	INNER JOIN SYS.INDEXES AS I ON T.OBJECT_ID = I.OBJECT_ID
	INNER JOIN SYS.PARTITIONS AS P ON I.OBJECT_ID = P.OBJECT_ID
		AND I.INDEX_ID = P.INDEX_ID
	INNER JOIN SYS.PARTITION_SCHEMES AS S ON I.DATA_SPACE_ID = S.DATA_SPACE_ID
	INNER JOIN SYS.PARTITION_FUNCTIONS AS F ON S.FUNCTION_ID = F.FUNCTION_ID
	LEFT JOIN SYS.PARTITION_RANGE_VALUES AS R ON F.FUNCTION_ID = R.FUNCTION_ID
		AND R.BOUNDARY_ID = P.PARTITION_NUMBER
	WHERE T.NAME = T1.NAME
		AND I.TYPE <= 1
	) D(PARTITIONCNT)
OUTER APPLY (
	SELECT 8 * SUM(A.USED_PAGES) AS 'INDEXSIZE(KB)'
	FROM SYS.INDEXES AS III
	INNER JOIN SYS.PARTITIONS AS P ON P.OBJECT_ID = III.OBJECT_ID
		AND P.INDEX_ID = I.INDEX_ID
	INNER JOIN SYS.ALLOCATION_UNITS AS A ON A.CONTAINER_ID = P.PARTITION_ID
	WHERE III.OBJECT_ID = T1.OBJECT_ID
		AND III.INDEX_ID = I.INDEX_ID
	) E(INDEXSIZEKB)
OUTER APPLY (
	SELECT TOP 1 *
	FROM (
		SELECT *
		FROM SYS.DM_DB_INDEX_OPERATIONAL_STATS(DB_ID(), NULL, NULL, NULL)
		) a
	WHERE OBJECT_ID = T1.OBJECT_ID
		AND INDEX_ID = I.INDEX_ID
		AND 1 = CASE 
			WHEN T1.TYPE_DESC = 'SQL_TABLE_VALUED_FUNCTION'
				THEN 0
			ELSE 1
			END
	) STAT2
OUTER APPLY (
	SELECT TOP 1 *
	FROM (
		SELECT *
		FROM SYS.DM_DB_INDEX_USAGE_STATS
		) A
	WHERE OBJECT_ID = T1.OBJECT_ID
		AND INDEX_ID = I.INDEX_ID
		AND DATABASE_ID = DB_ID()
	) STAT1
OUTER APPLY (
	SELECT TOP 1 *
	FROM (
		SELECT *
		FROM SYS.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL)
		) a
	WHERE OBJECT_ID = T1.OBJECT_ID
		AND INDEX_ID = I.INDEX_ID
		AND 1 = CASE 
			WHEN T1.TYPE_DESC = 'SQL_TABLE_VALUED_FUNCTION'
				THEN 0
			ELSE 1
			END
	) STAT3
WHERE IS_MS_SHIPPED = 0
	AND T1.NAME = CASE 
		WHEN @NAME = ''
			THEN T1.NAME
		ELSE @NAME
		END
ORDER BY 1
	, 2
	, 3
	, 4
	, 5
	, 6
	, 7
	, I.INDEX_ID
GO

EXEC sp_helpindex2
GO