MSSQL 인덱스 정보 보기 (DBA 관리용 시스템 프로시저)

MSSQL 인덱스 정보를 보는 프로시저이다

/************************************************************************************************

-- sp_index0

-- 해당 DB의 인덱스를 모두 보여준다.

************************************************************************************************/

USE master

GO

IF OBJECT_ID('sp_index0') IS NOT NULL

   DROP PROCEDURE sp_index0

GO

CREATE PROC sp_index0

  @objname VARCHAR(100) = NULL

AS

  SET NOCOUNT ON

  IF (@objname IS NULL)

  BEGIN

    SET @objname = 'all_indexes_output'

  END

  ELSE

  BEGIN

    IF NOT EXISTS(SELECT ID

                  FROM sysobjects

                  WHERE id = object_id(@objname)

                    AND xtype IN ('S','U', 'V')

                 )

    BEGIN

       RAISERROR('There is no ''%s'' object(xtype:s,u,v) in this database...', 16, 1,@objname)

       RETURN(1)

    END

  END

  

  CREATE TABLE #indexes_tbl

         ( tblname VARCHAR(255)

         , idxname VARCHAR(255)

         , idxdesc VARCHAR(1000)

         , idxcol VARCHAR(1000)

         )

  DECLARE @ignore_duplicate_keys VARCHAR(35) -- spt_values' name

        , @unique                VARCHAR(35)

        , @ignore_duplicate_rows VARCHAR(35)

        , @hypothetical          VARCHAR(35)

        , @statistics            VARCHAR(35)

        , @primary_key           VARCHAR(35)

        , @unique_key            VARCHAR(35)

        , @auto_create           VARCHAR(35)

        , @stats_no_recompute    VARCHAR(35)

  SELECT @ignore_duplicate_keys = name FROM master.dbo.spt_values 

  WHERE type = 'I' AND number = 1

  SELECT @unique = name FROM master.dbo.spt_values 

  WHERE type = 'I' AND number = 2

  SELECT @ignore_duplicate_rows  = name FROM master.dbo.spt_values 

  WHERE type = 'I' AND number = 4

  SELECT @hypothetical = name FROM master.dbo.spt_values 

  WHERE type = 'I' AND number = 32

  SELECT @statistics = name FROM master.dbo.spt_values 

  WHERE type = 'I' AND number = 64

  SELECT @primary_key = name FROM master.dbo.spt_values 

  WHERE type = 'I' AND number = 2048

  SELECT @unique_key = name FROM master.dbo.spt_values 

  WHERE type = 'I' AND number = 4096

  SELECT @auto_create = name FROM master.dbo.spt_values 

  WHERE type = 'I' AND number = 8388608

  SELECT @stats_no_recompute = name FROM master.dbo.spt_values 

  WHERE type = 'I' AND number = 16777216

  INSERT INTO #indexes_tbl

  SELECT o.name "Table Name"

       , i.name "Index Name"

        --bits 16 off, 1, 2, 16777216 on, located on group

       , CONVERT(VARCHAR(210)

               , CASE WHEN (i.status & 16)<>0 THEN 'clustered' 

                      ELSE 'nonclustered' 

                 END 

               + CASE WHEN (i.status & 1)<>0 THEN ', '+ @ignore_duplicate_keys 

                      ELSE '' 

                 END

               + CASE WHEN (i.status & 2)<>0 THEN ', '+ @unique 

                      ELSE '' 

                 END

               + CASE WHEN (i.status & 4)<>0 THEN ', '+ @ignore_duplicate_rows

                      ELSE '' 

                 END

               + CASE WHEN (i.status & 64)<>0 THEN ', '+ @statistics 

                      ELSE CASE WHEN (i.status & 32)<>0 THEN ', '+ @hypothetical 

                                ELSE '' 

                           END 

                 END

               + CASE WHEN (i.status & 2048)<>0 THEN ', '+ @primary_key 

                      ELSE '' 

                 END

               + CASE WHEN (i.status & 4096)<>0 THEN ', '+ @unique_key 

                      ELSE '' 

                 END

               + CASE WHEN (i.status & 8388608)<>0 THEN ', '+ @auto_create 

                      ELSE '' 

                 END

               + CASE WHEN (i.status & 16777216)<>0 THEN ', '+ @stats_no_recompute  

                      ELSE '' 

                 END

                ) "Index Description"

       , LEFT(ISNULL(INDEX_COL(o.name,indid, 1)+', ','') 

              + ISNULL(INDEX_COL(o.name,indid, 2)+ ', ','')

              + ISNULL(INDEX_COL(o.name,indid, 3)+', ','') 

              + ISNULL(INDEX_COL(o.name,indid, 4)+ ', ','')

              + ISNULL(INDEX_COL(o.name,indid, 5)+', ','')

              + ISNULL(INDEX_COL(o.name,indid, 6)+ ', ','')

              + ISNULL(INDEX_COL(o.name,indid, 7)+', ','')

              + ISNULL(INDEX_COL(o.name,indid, 8)+ ', ','')

             ,len(ISNULL(INDEX_COL(o.name,indid, 1)+', ','')

                  + ISNULL(INDEX_COL(o.name,indid, 2)+ ', ','')

                  + ISNULL(INDEX_COL(o.name,indid, 3)+', ','')

                  + ISNULL(INDEX_COL(o.name,indid, 4)+ ', ','')

                  + ISNULL(INDEX_COL(o.name,indid, 5)+', ','')

                  + ISNULL(INDEX_COL(o.name,indid, 6)+ ', ','')

                  + ISNULL(INDEX_COL(o.name,indid, 7)+', ','')

                  + ISNULL(INDEX_COL(o.name,indid, 8)+ ', ','')

                  ) - 1

             ) "Indexed Column Name"

  FROM sysindexes i INNER JOIN sysobjects o

                    ON i.id = o.id

  WHERE i.indid > 0 

    AND i.indid < 255

    AND o.type = 'U'

    --exclude autostatistic index

    AND (i.status & 64) = 0

    AND (i.status & 8388608) = 0

    AND (i.status & 16777216)= 0

  ORDER BY o.name

  

  IF (@objname = 'all_indexes_output')

    SELECT *

    FROM #indexes_tbl

  ELSE

    SELECT *

    FROM #indexes_tbl

    WHERE tblname = @objname

  DROP TABLE #indexes_tbl

  RETURN(0) --sp_index0

  

  SET NOCOUNT OFF

GO 

GRANT EXECUTE ON sp_index0 TO PUBLIC

GO