I'm trying a very simple drop column
statement:
alter table MyTable drop column MyColumn
and receiving several errors along the lines of
Msg 5074, Level 16, State 1, Line 1
The statistics '_dta_stat_1268251623_3_2' is dependent on column 'MyColumn'.
followed ultimately by
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE DROP COLUMN MyColumn failed because one or more objects access this column.
I didn't think statistics prevent a column from being dropped. Do they? If so, since these are apparently auto-created statistics I can't depend on the names being the same across multiple copies of the same database, so how could I drop all such statistics in an upgrade script to be executed on a different database?
The code proposed in JNK answer does not work, but the idea is good. If you want to delete all user created statistics this my tested solution :
DECLARE @sql NVARCHAR(MAX)
DECLARE statCursor CURSOR FOR
SELECT
'DROP STATISTICS ' + QUOTENAME(SCHEMA_NAME(t.schema_id))
+ '.' + QUOTENAME(t.name)
+ '.' + QUOTENAME(st.name) AS sql
FROM
sys.stats AS st
INNER JOIN sys.tables AS t
ON st.object_id = t.object_id
WHERE
st.user_created = 1
ORDER BY 1;
OPEN statCursor;
FETCH NEXT FROM statCursor INTO @sql
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @sql
EXEC sp_executesql @sql
FETCH NEXT FROM statCursor INTO @sql
END
CLOSE statCursor
DEALLOCATE statCursor
Auto-generated statistics that I have seen all either have the name of the index they represent OR start with something like WA_Sys_
.
Are you 100% sure this is not a set of custom stats someone set up?
Check this:
select *
FROM sys.stats WHERE name = '_dta_stat_1268251623_3_2'
...and see what the user_created
field indicates.
Per comment:
This is untested but you could try something like:
exec sp_MSforeachdb '
use ?
DECLARE @SQL varchar(max) = ''''
select @SQL = @SQL + ''DROP STATISTICS '' + OBJECT_NAME(c.object_id) + ''.'' + s.name + CHAR(10) + CHAR(13)
from sys.stats s
INNER JOIN sys.stats_columns sc
ON sc.stats_id = s.stats_id
INNER JOIN sys.columns c
ON c.column_id = sc.column_id
WHERE c.name = ''ClaimNbr''
--and s.user_created = 1
PRINT @SQL'
Change the PRINT
to an EXEC
if it looks good.
sp_msforeachdb
is a cursor in the background but the rest of the logic you can do as a set.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With