SS2000 - 해당 DB의 모든 인덱스 정보 끌어오기

해당DB의_모든_인덱스_정보_끌어오기.sql

CREATE VIEW dbo.INDEXVIEW

AS
/****************************************************************************************************
** Creation Date: ?
** Modif Date   : Nov. 27, 2002
** Created By   : avigneau
** Database     : any
** Description  : Reports on all indexes and / or heaps on user tables within a database
** Parameters   : none
** Compatibility: SQL Server 6.X, 7.0, 2000
** Remark       : System tables are used to be compatible with version 6.x.
        But I believe it would still be difficult to obtain the same results
                  using INFORMATION_SCHEMA views and new object and system property functions.
** Example      : SELECT 'Showing All Indexes' AS Comments, I.*
        FROM dbo.INDEXVIEW I
        SELECT 'Showing Tables with Missing Clustered index' AS Comments, I.*
        FROM dbo.INDEXVIEW I
        WHERE ClusterType = 'HEAP'
      SELECT 'Showing Tables with Missing Primary Keys' AS Comments,  I.*
        FROM dbo.INDEXVIEW I
         LEFT OUTER JOIN dbo.INDEXVIEW I2
          ON I.TableID = I2.TableID AND I2.UniqueType = 'PRIMARY KEY'
        WHERE I2.TableID IS NULL  
      SELECT 'Showing Possible Redundant Index keys' AS Comments,  I.*
        FROM dbo.INDEXVIEW I
         JOIN dbo.INDEXVIEW I2
          ON I.TableID = I2.TableID AND I.ColName1 = I2.ColName1
          AND I.IndexName <> I2.IndexName
      SELECT 'Showing Possible Reverse Index keys' AS Comments,  I.*
       FROM dbo.INDEXVIEW I
        JOIN dbo.INDEXVIEW I2
         ON I.TableID = I2.TableID AND I.ColName1 = I2.ColName2
         AND I.ColName2 = I2.ColName1 AND I.IndexName <> I2.IndexName
************************************************************************************************/

SELECT o.id AS TableID ,u.name Owner,o.name TableName,
  i.Indid AS IndexID
  , CASE i.name
   WHEN o.name THEN '** NONE **'
  ELSE i.name END AS IndexName,
       CASE i.indid
   WHEN 1 THEN 'CLUSTERED'
   WHEN 0 THEN 'HEAP'
  ELSE 'NONCLUSTERED' END AS ClusterType,
  CASE
     WHEN (i.status & 2048) > 0 THEN 'PRIMARY KEY'
   WHEN (i.status & (2|4096)) > 0 THEN 'UNIQUE'
  ELSE '' END AS UniqueType,
    CASE      
   WHEN (i.status & (2048)) > 0
     OR ((i.status & (4096)) > 0 )
    THEN 'CONSTRAINT'
   WHEN i.indid = 0 THEN ' '
  ELSE 'INDEX' END AS IndexType,
-- This following part is non essential
-- It is a pre char aggregate I use in other scripts
-- to generate create and drop scripts
  CASE      
   WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 1) IS NULL THEN ''
  ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 1) END +
      CASE      
   WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 2) IS NULL THEN ''
  ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,2) END +
  CASE      
   WHEN INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 3) IS NULL THEN ''
  ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,3) END +
    CASE      
   WHEN INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 4) IS NULL THEN ''
  ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,4) END +
  CASE      
   WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 5) IS NULL THEN ''
  ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,5) END +
      CASE      
   WHEN INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 6) IS NULL THEN ''
  ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,6) END +
      CASE      
   WHEN INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 7) IS NULL THEN ''
  ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 7) END +
      CASE      
   WHEN INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 8) IS NULL THEN ''
  ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,8) END +
      CASE      
   WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 9) IS NULL THEN ''
  ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,9) END +
      CASE      
   WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 10) IS NULL THEN ''
  ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,10) END +
      CASE      
   WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 11) IS NULL THEN ''
  ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,11) END +
      CASE      
   WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 12) IS NULL THEN ''
  ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,12) END +
      CASE      
   WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 13) IS NULL THEN ''
  ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,13) END +
      CASE      
   WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 14) IS NULL THEN ''
  ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,14) END +
      CASE      
   WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 15) IS NULL THEN ''
  ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,15) END +
      CASE      
   WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 16) IS NULL THEN ''
  ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,16) END AS AllColName,
--
      CASE      
   WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 1) IS NULL THEN ''
  ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,1) END  AS ColName1,
      CASE      
   WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 2) IS NULL THEN ''
  ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,2) END AS ColName2,
  CASE      
   WHEN INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 3) IS NULL THEN ''
  ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,3) END AS ColName3,
      CASE      
   WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 4) IS NULL THEN ''
  ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,4) END AS ColName4,
      CASE      
   WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 5) IS NULL THEN ''
  ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,5) END AS ColName5,
      CASE      
   WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 6) IS NULL THEN ''
  ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,6) END AS ColName6,
      CASE      
   WHEN INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 7) IS NULL THEN ''
  ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,7) END AS ColName7,
      CASE      
   WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 8) IS NULL THEN ''
  ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,8) END AS ColName8,
      CASE      
   WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 9) IS NULL THEN ''
  ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,9) END AS ColName9,
      CASE      
   WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 10) IS NULL THEN ''
  ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,10) END AS ColName10,
    CASE      
   WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 11) IS NULL THEN ''
  ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,11) END AS ColName11,
      CASE      
   WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 12) IS NULL THEN ''
  ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,12) END AS ColName12,
      CASE      
   WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 13) IS NULL THEN ''
  ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,13) END AS ColName13,
      CASE      
   WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 14) IS NULL THEN ''
  ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,14) END AS ColName14,
      CASE      
   WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 15) IS NULL THEN ''
  ELSE INDEX_COL(u.name+'.'+QUOTENAME (o.name), i.indid,15) END AS ColName15,
      CASE      
   WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 16) IS NULL THEN ''
  ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,16) END AS ColName16
 FROM sysobjects o (NOLOCK)
 LEFT OUTER JOIN sysindexes i (NOLOCK)
  ON o.id = i.id
   JOIN sysusers u (NOLOCK)
  ON o.uid = u.uid
 WHERE o.type = 'U' AND i.indid < 255
 AND o.name NOT IN ('dtproperties')
 AND i.name NOT LIKE '_WA_Sys_%'
-- because of SQL Server 7.0

GO

SELECT 'Showing All Indexes' AS Comments, I.*
 FROM dbo.INDEXVIEW I
GO
SELECT 'Showing Tables with Missing Clustered index' AS Comments, I.*
 FROM dbo.INDEXVIEW I
 WHERE ClusterType = 'HEAP'
GO
SELECT 'Showing Tables with Missing Primary Keys' AS Comments,  I.*
 FROM dbo.INDEXVIEW I
 LEFT OUTER JOIN dbo.INDEXVIEW I2
  ON I.TableID = I2.TableID
  AND I2.UniqueType = 'PRIMARY KEY'
 WHERE I2.TableID IS NULL
GO
SELECT 'Showing Possible Redundant Index keys' AS Comments ,  I.*
 FROM dbo.INDEXVIEW I
 JOIN dbo.INDEXVIEW I2
  ON I.TableID = I2.TableID
  AND I.ColName1 = I2.ColName1
  AND I.IndexName <> I2.IndexName
 ORDER BY I.TableName,I.IndexName
GO
SELECT 'Showing Possible Reverse Index keys' AS Comments ,  I.*
 FROM dbo.INDEXVIEW I
 JOIN dbo.INDEXVIEW I2
  ON I.TableID = I2.TableID
  AND I.ColName1 = I2.ColName2
  AND I.ColName2 = I2.ColName1
  AND I.IndexName <> I2.IndexName
GO
DROP VIEW INDEXVIEW
GO

http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=PDSCODE&intSeq=20