테이블 정보 프로시저

CREATE PROCEDURE sp_IndexInfo

@tablename sysname

AS

SELECT @tablename AS '테이블이름',

I.name AS '인덱스이름',

I.type_desc AS '인덱스타입',

A.data_pages AS '페이지개수',

A.data_pages * 8 AS '크기(KB)',

P.rows AS '행개수'

FROM sys.indexes I

INNER JOIN sys.partitions P

ON P.object_id = I.object_id

AND OBJECT_ID(@tablename) = I.object_id

AND I.index_id = P.index_id

INNER JOIN sys.allocation_units A

ON A.container_id = P.hobt_id

GO