[SQL SERVER][TSQL]刪除自動建立統計值

[SQL SERVER][TSQL]刪除自動建立統計值

刪除某資料表中所有自動建立統計值

以下請自行更改 Scheme & Table Name

 

 


declare @result table
(
serial int identity(1,1),
owner varchar(100),
tname varchar(500),
sname varchar(100)
)

insert into @result
select sch.name as 'Owner',obj.name as '資料表名稱',st.name as '統計值名稱'
from sys.stats st 
inner join sys.objects obj
on st.object_id=obj.object_id
JOIN sys.schemas sch 
ON obj.schema_id = sch.schema_id 
where st.stats_id > 0 
and  st.stats_id < 255 
and  st.auto_created=1
and object_name(obj.object_id)='product'

and sch.name='dbo'



declare @step int,@maxcount int
declare @cursql nvarchar(4000),@ownername varchar(100),
@tname varchar(100),@sname varchar(500)
set @step=1
set @cursql=''
select @maxcount=count(*) from @result

while(@step<=@maxcount)
begin
select @ownername=owner,@sname=sname ,@tname=tname 
from @result where serial=@step 
set @cursql=@cursql+' drop statistics ' +QUOTENAME(@ownername)+'.'+
QUOTENAME(@tname)+'.'+QUOTENAME(@sname);
exec sp_executesql @cursql ;
set @step=@step+1 ; 
end