Sql2005 deletes all indexes and statistics of a specified table
Sql2005 deletes all indexes and statistics of a specified table
The Code is as follows:
------------------------------------------------------------------------
-- Author: HappyFlyStone
-- Date: 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) -- the table for which statistics or indexes need to be deleted
AS
BEGIN
DECLARE @ drop_idx_string nvarchar (4000) -- stores dynamically organized DROPS index/stats syntax
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' ------ current table: ''% s' does not exist! ', 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 (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 (8388704)
END
IF Len (@ drop_idx_string)> 0
BEGIN
PRINT n' ------ statistics Delete list ------'
PRINT @ drop_idx_string + ';'
EXECUTE (@ drop_idx_string + ';')
PRINT n' ------ statistics deletion ends ------'
END
If exists (SELECT 1 FROM sysindexes
WHERE id = @ tabname AND indid BETWEEN 1 AND 254
AND status not in (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 (8388704)
And objectproperty (OBJECT_ID (name), 'isconstraint') is null -- the process does not process CONSTRAINTS
END
PRINT n' ------ index deletion list ------'
PRINT (@ drop_idx_string + ';')
EXEC (@ drop_idx_string + ';')
PRINT ('...' + CHAR (13) + CHAR (10) + '......')
PRINT n' ------ index deletion ends ------'
END
GO
Create clustered index idx_id on ta (id)
Create index idx_col on ta (col)
Go
Sp_DropAllIndex 'ta'
/*
------ Index deletion list ------
Drop index ta. idx_id;
Drop index ta. idx_col;
......
......
------ Index deletion ends ------
*/
