September 11, 2006 at 4:09 am
sql 2005
I am trying to drop a column but cant because stats exist which include that column. I know i can go through and drop the stats manually with T-SQL but is there a way i can drop them automatically/dynamically with T-SQL or a script which finds those which include my column and removes it from them. I dont remember having this problem with SQL 2000 did that auto drop stats when you dropped a column?
Thanks,
Jules
September 12, 2006 at 3:43 pm
September 13, 2006 at 7:10 am
What error message are you getting when you are trying to drop the stats?
How did those stats get created?
September 13, 2006 at 7:50 am
September 13, 2006 at 8:24 am
I can't make the full script for you, but this can get you started pretty good. All that is left is generating the drop commands and executing them :
SELECT
DISTINCT
object_name(i.id) as TableName
, i.Name as StatName
, c.name as ColName
FROM
sys.SysIndexes i
inner join sys.SysIndexKeys k
on i.ID = k.id
inner join sys.SysColumns c
on k.id = c.id and c.colid = k.colid
WHERE
IndexProperty(i.id, i.name, 'IsStatistics') = 1
AND OBJECTPROPERTY (i.id, 'IsMsShipped') = 0
AND i.id = Object_id('YourTableName')
AND c.name = 'YourColumnName'
September 13, 2006 at 8:25 am
As for the setting change, I have no idea what it could be... I'm sure there's a way but without the exact error message I can't search in that direction.
September 13, 2006 at 8:37 am
September 13, 2006 at 8:43 am
Alright, pls post it back once you get it working.. I'm sure somebody else is gonna need this someday.
September 13, 2006 at 9:21 am
--This code will drop all stats on a given table/column combination
declare
@TableName varchar(50), @ColumnName varchar(50), @sql varchar(max), @no tinyint, @i tinyint
set
@TableName = '' --enter table name here
set
@ColumnName = '' --enter column name here
set
@i = 1
declare
@tab table(id int identity, StatsName varchar(50))
insert
select
distinct
i
.Name as StatsName
from
sys.SysIndexes i
join
sys.SysIndexKeys k
on i.ID = k.id
join
sys.SysColumns c
on k.id = c.id
and c.colid = k.colid
where
IndexProperty(i.id, i.name, 'IsStatistics') = 1
and
objectproperty (i.id, 'IsMsShipped') = 0
and
i.id = Object_id(@TableName)
and
c.name = @ColumnName
set
@no = scope_identity()
while
@i <=@no
begin
select
@sql = 'drop statistics ' + @TableName + '.' +StatsName +char(10)
from
where
id = @i
exec
(@sql)
@sql
set
@i = @i +1
end
September 13, 2006 at 9:29 am
Thanx for the script. Too bad we can't figure out why you need the script in the first place.
September 13, 2006 at 9:36 am
September 13, 2006 at 9:52 am
All I can find is that this happens when the stats was manually created. It then becomes a valid dependant object that must be dropped first (a little like schemabinding works).
September 13, 2006 at 9:56 am
September 13, 2006 at 10:38 am
What was its real name?
September 13, 2006 at 10:45 am
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply