declare @stmt varchar(8000); set @stmt=''; select @stmt=@stmt+'drop index AAA on '+quotename(name)+';' from sys.tables where name like 'E%'; -- print @stmt; exec(@stmt);
sp_msforeachtable 'IF ''?'' LIKE ''E%'' DROP INDEX [?].AAA'try
EXEC sp_msforeachtable 'IF PARSENAME("?",1) LIKE "tb_%" AND EXISTS (SELECT * FROM sys.indexes WHERE OBJECT_ID = OBJECT_ID(PARSENAME("?",1)) AND name = "AAA") BEGIN DROP INDEX AAA ON ?; PRINT "DROP INDEX AAA ON ?" END'
把 tb_% 改成 E%
刚实测了一下,4#的代码有问题,以下代码在SQL2005以上版本可用:EXEC sp_msforeachtable 'IF PARSENAME(''?'',1) LIKE ''T%'' BEGIN TRY DROP INDEX ?.AAA END TRY BEGIN CATCH END CATCH'
declare @stmt varchar(8000);
set @stmt='';
select @stmt=@stmt+'drop index AAA on '+quotename(name)+';'
from sys.tables where name like 'E%';
-- print @stmt;
exec(@stmt);
EXEC sp_msforeachtable 'IF PARSENAME("?",1) LIKE "tb_%"
AND EXISTS (SELECT * FROM sys.indexes WHERE OBJECT_ID = OBJECT_ID(PARSENAME("?",1)) AND name = "AAA")
BEGIN DROP INDEX AAA ON ?; PRINT "DROP INDEX AAA ON ?" END'