[SQL] 快速找出資料庫中未建立INDEX的Table

2010-02-08

5791
0

快速找出資料庫中未建立INDEX的Table

SELECT  TOP 100
   REPLICATE(' ',4000) AS COLNAMES ,
   OBJECT_NAME(I.ID) AS TABLENAME,
   I.ID AS TABLEID,
   I.INDID AS INDEXID,
   I.NAME AS INDEXNAME,
   I.STATUS,
   INDEXPROPERTY (I.ID,I.NAME,'ISUNIQUE') AS ISUNIQUE,
   INDEXPROPERTY (I.ID,I.NAME,'ISCLUSTERED') AS ISCLUSTERED,
   INDEXPROPERTY (I.ID,I.NAME,'INDEXFILLFACTOR') AS INDEXFILLFACTOR
   INTO #TMP
   FROM SYSINDEXES I
   WHERE I.INDID > 0
   AND I.INDID < 255
   AND (I.STATUS & 64)=0
--uncomment below to eliminate PK or UNIQUE indexes;
--what i call 'normal' indexes
   --AND   INDEXPROPERTY (I.ID,I.NAME,'ISUNIQUE')       =0
   --AND   INDEXPROPERTY (I.ID,I.NAME,'ISCLUSTERED') =0

DECLARE
   @ISQL VARCHAR(4000),
   @TABLEID INT,
   @INDEXID INT,
   @MAXTABLELENGTH INT,
   @MAXINDEXLENGTH INT
   --USED FOR FORMATTING ONLY
     SELECT @MAXTABLELENGTH=MAX(LEN(TABLENAME)) FROM #TMP
     SELECT @MAXINDEXLENGTH=MAX(LEN(INDEXNAME)) FROM #TMP

     DECLARE C1 CURSOR FOR
       SELECT TABLEID,INDEXID FROM #TMP
     OPEN C1
       FETCH NEXT FROM C1 INTO @TABLEID,@INDEXID
         WHILE @@FETCH_STATUS <> -1
           BEGIN
     SET @ISQL = ''
     SELECT @ISQL=@ISQL + ISNULL(SYSCOLUMNS.NAME,'') + ',' FROM SYSINDEXES I
     INNER JOIN SYSINDEXKEYS ON I.ID=SYSINDEXKEYS.ID AND I.INDID=SYSINDEXKEYS.INDID
     INNER JOIN SYSCOLUMNS ON SYSINDEXKEYS.ID=SYSCOLUMNS.ID AND SYSINDEXKEYS.COLID=SYSCOLUMNS.COLID
     WHERE I.INDID > 0
     AND I.INDID < 255
     AND (I.STATUS & 64)=0
     AND I.ID=@TABLEID AND I.INDID=@INDEXID
     ORDER BY SYSCOLUMNS.COLID
     UPDATE #TMP SET COLNAMES=@ISQL WHERE TABLEID=@TABLEID AND INDEXID=@INDEXID

     FETCH NEXT FROM C1 INTO @TABLEID,@INDEXID
          END
       CLOSE C1
       DEALLOCATE C1
   --AT THIS POINT, THE 'COLNAMES' COLUMN HAS A TRAILING COMMA
   UPDATE #TMP SET COLNAMES=LEFT(COLNAMES,LEN(COLNAMES) -1)

   SELECT * FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_NAME NOT IN(
   SELECT
     --'' + UPPER(INDEXNAME) + '' AS INDEXNAME
     '' + UPPER(TABLENAME) + '' AS TABLENAME
     --,'' + UPPER(COLNAMES) + '' AS COLUMNNAME
   FROM #TMP)

    --SELECT * FROM #TMP
   DROP TABLE #TMP

-----------------------
Share is Power

回首頁