常見索引操作

常見索引操作：
--显示指定表的数据和索引的碎片信息
--察看索引統計信息
--察看對應表存在那些索引及相關信息
--創建有名主鍵索引非聚集
--創建表，同時創建主鍵索引
--删除主键
--創建索引
--删除索引
--重建索引
--重建指定数据库中表的一个或多个索引
--一个删除指定表的所有索引和统计的过程(周老大)
-----------------------------------------------------------------------
-- DBCC showcontig
--显示指定表的数据和索引的碎片信息

--察看對應表存在那些統計及相關信息
SELECT name,rowmodctr,* --+ ('DROP STATISTICS DGMOCPU.'+name)
       FROM sysindexes
WHERE id=OBJECT_ID('DGMOCPU') AND indid BETWEEN 1 AND 254
                  AND status IN (96,10485856,8388704)

---察看索引統計信息
DBCC SHOW_STATISTICS (DGMOCPB, PK_DGMOCPB)
--察看對應表存在那些索引及相關信息
select name,object_name(id)as tableName,* from  sysindexes where
id=OBJECT_ID('DGMOCPU') and --(通過表名稱查詢)
--name=object_name('DGMOCPU_PK') and --(通過索引名稱查詢)
indid BETWEEN 1 AND 254
 AND status NOT IN (96,10485856,8388704)
-- AND OBJECTPROPERTY (OBJECT_ID(name),'IsConstraint') IS NULL --过程不顯示

CONSTRAINTS
-----------------------------------------------------------------------------
--創建有名主鍵索引非聚集
alter table dgmocpu add constraint PK_DGMOCPU_PR primary key nonCLUSTERED 

(PU001,PU002,PU003,PU005)

--删除主键
alter table dgmocpU drop constraint PK_DGMOCPU_PR

--創建索引(非聚集)
create nonclustered index IX_DGMOCPU on dgmocpU(PU001 ASC,PU002 ASC)

--創建索引(聚集)
create clustered index IC_DGMOCPU on dgmocpU(PUDATE desc)

--删除索引
drop INDEX dgmocpU.IX_DGMOCPU

 

--禁用约束
alter table tablename
nocheck constraint constraintname

 

--啓用约束
alter table tablename
check constraint constraintname

 

--有名稱的主鍵+聚集索引
ALTER TABLE [dbo].[agent_post_reg] WITH NOCHECK ADD
        CONSTRAINT [PK_agent_post_reg] PRIMARY KEY  CLUSTERED
        (
                [agnet_post_id]
        ) WITH  FILLFACTOR = 90  ON [PRIMARY]


--創建表，同時創建主鍵索引
CREATE   TABLE     CM_TEMP  ( 
                PART_ID VARCHAR2(20), 
                COST_CODE VARCHAR2(20), 
                QTY NUMBER(12) 
                PRIMARY   KEY   (PART_ID,COST_CODE)
            )
CREATE   INDEX   IDX_CM_TEMP   ON   CM_TEMP(PART_ID,COST_CODE)
           
--重組索引 (碎片率30%内)
ALTER INDEX PK_DGMOCPU_PR ON DGMOCPU
REORGANIZE

--重建索引 (大约30%)
ALTER INDEX PK_DGMOCPU_PR ON DGMOCPU
REBUILD


--重建指定数据库中表的一个或多个索引。
dbcc dbreindex (tablename)

--一个删除指定表的所有索引和统计的过程
------------------------------------------------------------------------
-- Author : HappyFlyStone
-- Date   : 2009-09-05 00:57:10
-- Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
--      Apr 14 2006 01:12:25
--          Copyright (c) 1988-2005 Microsoft Corporation
--          Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
--    
------------------------------------------------------------------------

IF EXISTS (SELECT name FROM sysobjects WHERE id = OBJECT_ID('sp_DropAllIndex')
       AND OBJECTPROPERTY(OBJECT_ID('sp_DropAllIndex'),'IsProcedure')=1)
   DROP PROCEDURE sp_DropAllIndex
GO
CREATE PROCEDURE sp_DropAllIndex
    @tabname nvarchar(150) -- 需要删除统计或索引的表
AS
BEGIN
    DECLARE @drop_idx_string nvarchar(4000) -- 存放动态组织而成的DROPS 

index/stats 语法
    SET NOCOUNT ON
    --  check table
    IF NOT EXISTS (SELECT 1
                  FROM INFORMATION_SCHEMA.TABLES
                  WHERE table_type = 'base table' AND table_name = @tabname)
    BEGIN
        RAISERROR(N'------当前表：''%s'' 不存在!',16, 1, @tabname)
        RETURN (1)
    END
    SET @tabname = OBJECT_ID(@tabname)
    IF EXISTS (SELECT 1
               FROM sysindexes
               WHERE id=@tabname AND indid BETWEEN 1 AND 254
                   AND status IN (96,10485856,8388704))
    BEGIN
        SELECT @drop_idx_string = isnull(@drop_idx_string+';','')
                     + ('DROP STATISTICS '+OBJECT_NAME(@tabname)+'.'+name)
       FROM sysindexes
       WHERE id=@tabname AND indid BETWEEN 1 AND 254
                 AND status IN (96,10485856,8388704)
    END
    IF Len(@drop_idx_string) > 0
    BEGIN
       PRINT N'------统计删除列表------'
       PRINT @drop_idx_string+';'
       EXECUTE(@drop_idx_string+';')
       PRINT N'------统计删除结束------'
    END
    IF EXISTS (SELECT 1 FROM sysindexes
          WHERE id=@tabname AND indid BETWEEN 1 AND 254
                   AND status NOT IN (96,10485856,8388704))
    BEGIN
       SET @drop_idx_string = NULL
        select @drop_idx_string = isnull(@drop_idx_string+';'+CHAR(13)+CHAR

(10),'')
                                + ('DROP INDEX '+OBJECT_NAME(@tabname)+'.'+name)
       FROM sysindexes
       WHERE id=@tabname AND indid BETWEEN 1 AND 254
           AND status NOT IN (96,10485856,8388704)
           AND OBJECTPROPERTY (OBJECT_ID(name),'IsConstraint') IS NULL--过程不处

理CONSTRAINTS
    END
    PRINT N'------索引删除列表------'
    PRINT (@drop_idx_string+';')
    EXEC( @drop_idx_string+';')
    PRINT ('......'+CHAR(13)+CHAR(10)+'......')
    PRINT N'------索引删除结束------'
END
GO
create clustered index idx_id on ta(id)
create index idx_col on ta(col)
go
sp_DropAllIndex 'ta'
/*
------索引删除列表------
DROP INDEX ta.idx_id;
DROP INDEX ta.idx_col;
......
......
------索引删除结束------
*/