一個(gè)刪除指定表的所有索引和統(tǒng)計(jì)的過(guò)程
------------------------------------------------------------------------
-- 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) -- 需要?jiǎng)h除統(tǒng)計(jì)或索引的表
AS
BEGIN
DECLARE @drop_idx_string nvarchar(4000) -- 存放動(dòng)態(tài)組織而成的DROPS index/stats 語(yǔ)法
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'------當(dāng)前表:''%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'------統(tǒng)計(jì)刪除列表------'
PRINT @drop_idx_string+';'
EXECUTE(@drop_idx_string+';')
PRINT N'------統(tǒng)計(jì)刪除結(jié)束------'
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--過(guò)程不處理CONSTRAINTS
END
PRINT N'------索引刪除列表------'
PRINT (@drop_idx_string+';')
EXEC( @drop_idx_string+';')
PRINT ('......'+CHAR(13)+CHAR(10)+'......')
PRINT N'------索引刪除結(jié)束------'
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;
......
......
------索引刪除結(jié)束------
*/
相關(guān)文章
Sqlserver 2005附加數(shù)據(jù)庫(kù)時(shí)出錯(cuò)提示操作系統(tǒng)錯(cuò)誤5(拒絕訪問(wèn))錯(cuò)誤5120的解決辦法
最近幾天從網(wǎng)上找了幾個(gè)asp.net的登錄案例想要研究研究代碼,結(jié)果在用Sql Server2005附加數(shù)據(jù)庫(kù)文件時(shí)彈出錯(cuò)誤信息提示5120錯(cuò)誤,這里簡(jiǎn)單說(shuō)下解決方法,需要的朋友可以參考下2013-11-11sqlserver 中charindex/patindex/like 的比較
sqlserver 中charindex/patindex/like 的比較,大家可以看下根據(jù)需要自行選擇。2009-09-09SQLServer 2005數(shù)據(jù)庫(kù)連接字符串 連接sql2005必備資料
現(xiàn)在好多朋友開(kāi)始用sqlserver 2005了,因?yàn)楦鷖ql2000有區(qū)別,所以這里整理了下,需要的朋友可以參考下。2010-04-04SQL Server 中 RAISERROR 的用法詳細(xì)介紹
這篇文章主要介紹了SQL Server 中 RAISERROR 的用法詳細(xì)介紹的相關(guān)資料,需要的朋友可以參考下2016-11-11sql2005數(shù)據(jù)導(dǎo)出方法(使用存儲(chǔ)過(guò)程導(dǎo)出數(shù)據(jù)為腳本)
在數(shù)據(jù)庫(kù)中使用下面的腳本創(chuàng)建存儲(chǔ)過(guò)程,然后執(zhí)行存儲(chǔ)過(guò)程,參數(shù)為表名,就可以把表的數(shù)據(jù)輸出為SQL腳本2014-01-01SQL Server 2005 中使用 Try Catch 處理異常
本文主要對(duì)比了Sql Server 2000和Sql Server 2005對(duì)異常處理的方法,Sql Server 2005 之后的TRY CATCH捕捉異常,更類(lèi)似C#、JAVA等語(yǔ)言,更容易被程序員理解。2016-05-05sql2005開(kāi)發(fā)版 沒(méi)有任何功能可以安裝
sql2005開(kāi)發(fā)版 選擇的功能中沒(méi)有任何功能可以安裝或升級(jí)問(wèn)題終級(jí)解決方法2009-08-08