[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