TABLE cannot be renamed because the object participates in enforced dependencies

Question

TABLE cannot be renamed because the object participates in enforced dependencies

Jeff Smith-292086

SSC Veteran

Points: 288
More actions
February 2, 2010 at 8:49 pm

#137806

I am trying to rename a few tables using sp_rename and run into the following error:
Msg 15336, Level 16, State 1, Procedure sp_rename, Line 458
Object 'Ven_UserDownload' cannot be renamed because the object participates in enforced dependencies.
I can execute the following SQL:
select o.name as ObjName, r.name as ReferencedObj
from sys.sql_dependencies d
join sys.objects o on o.object_id=d.object_id
join sys.objects r on r.object_id=d.referenced_major_id
where d.class=1
AND r.name = 'Ven_UserDownload'
And get these results:
_dta_mv_81Ven_UserDownload
_dta_mv_2Ven_UserDownload
_dta_mv_3Ven_UserDownload
_dta_mv_3Ven_UserDownload
_dta_mv_2Ven_UserDownload
_dta_mv_3Ven_UserDownload
_dta_mv_2Ven_UserDownload
_dta_mv_3Ven_UserDownload
I tried to delete the statistics like this:
drop statistics [Ven_UserDownload].[_dta_index_Ven_UserDownload_5_1229247434__K1_K2_4]
drop statistics [Ven_UserDownload].[_dta_index_Ven_UserDownload_5_1229247434__K1_K2_4_6]
drop statistics [Ven_UserDownload].[_dta_index_Ven_UserDownload_5_1229247434__K1_K6]
drop statistics [Ven_UserDownload].[_dta_index_Ven_UserDownload_5_1229247434__K2]
drop statistics [Ven_UserDownload].[_dta_index_Ven_UserDownload_5_1229247434__K2_1_4]
drop statistics [Ven_UserDownload].[_dta_index_Ven_UserDownload_5_1229247434__K2_1_4_6]
drop statistics [Ven_UserDownload].[_dta_index_Ven_UserDownload_5_1229247434__K5]
drop statistics [Ven_UserDownload].[_dta_index_Ven_UserDownload_5_1229247434__K6_K1]
And get the following errors:
Msg 3739, Level 11, State 1, Line 1
Cannot DROP the index 'Ven_UserDownload._dta_index_Ven_UserDownload_5_1229247434__K1_K2_4' because it is not a statistics collection.
Msg 3739, Level 11, State 1, Line 2
Cannot DROP the index 'Ven_UserDownload._dta_index_Ven_UserDownload_5_1229247434__K1_K2_4_6' because it is not a statistics collection.
Msg 3739, Level 11, State 1, Line 3
Cannot DROP the index 'Ven_UserDownload._dta_index_Ven_UserDownload_5_1229247434__K1_K6' because it is not a statistics collection.
Msg 3739, Level 11, State 1, Line 4
Cannot DROP the index 'Ven_UserDownload._dta_index_Ven_UserDownload_5_1229247434__K2' because it is not a statistics collection.
Msg 3739, Level 11, State 1, Line 5
Cannot DROP the index 'Ven_UserDownload._dta_index_Ven_UserDownload_5_1229247434__K2_1_4' because it is not a statistics collection.
Msg 3739, Level 11, State 1, Line 6
Cannot DROP the index 'Ven_UserDownload._dta_index_Ven_UserDownload_5_1229247434__K2_1_4_6' because it is not a statistics collection.
Msg 3739, Level 11, State 1, Line 8
Cannot DROP the index 'Ven_UserDownload._dta_index_Ven_UserDownload_5_1229247434__K5' because it is not a statistics collection.
Msg 3739, Level 11, State 1, Line 9
Cannot DROP the index 'Ven_UserDownload._dta_index_Ven_UserDownload_5_1229247434__K6_K1' because it is not a statistics collection.
I found the following SQL and tried it without any results:
DECLARE @strSQL nvarchar(1024)
DECLARE @objid int
DECLARE @indid tinyint
DECLARE ITW_Stats CURSOR FOR SELECT id, indid FROM sysindexes WHERE name LIKE 'hind_%' ORDER BY name
OPEN ITW_Stats
FETCH NEXT FROM ITW_Stats INTO @objid, @indid
WHILE (@@FETCH_STATUS <> -1)
BEGIN
SELECT @strSQL = (SELECT case when INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 1 then 'drop statistics [' else 'drop index [' end + OBJECT_NAME(i.id) + '].[' + i.name + ']'
FROM sysindexes i join sysobjects o on i.id = o.id
WHERE i.id = @objid and i.indid = @indid AND
(INDEXPROPERTY(i.id, i.name, 'IsHypothetical') = 1 OR
(INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 1 AND
INDEXPROPERTY(i.id, i.name, 'IsAutoStatistics') = 0)))
EXEC(@strSQL)
FETCH NEXT FROM ITW_Stats INTO @objid, @indid
END
CLOSE ITW_Stats
DEALLOCATE ITW_Stats
I have dropped all constraints and indexes for the table, but still cannot drop the statistics. Does anyone have any suggestions to drop these statistics?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

I don't think those are statistics. From the naming, they're DTA-created indexes, either real or hypothetical. Try DROP INDEX.

btw, I don't think that the "enforced dependencies" refers to indexes. Check for foreign key constraints.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 2

I agree with Gail. It sounds like SQL is preventing you from renaming the objects because of constrainsts to other tables in the database.

Joie Andrew
"Since 1982"

Jeff Smith-292086 SSC Veteran Points: 288 More actions · Answer 3

I found the issue. Apparently Database Tuning Advisor created many views for the table that were dependant. After removing the views, issue is resolved. Thanks for the help.

solidlava SSC Rookie Points: 39 More actions · Answer 4

how did you identify which views were created by the db tuning advisor, or was it really obvious, I am wondering if my setup for the Memory Optimization Advisor is doing the same thing I have the same error

p bergeron SSC Enthusiast Points: 129 More actions · Answer 5

Check for indexed views... Those view ARE NOT the nicest thing since slice bread.