ALTER TABLE DROP COLUMN dept failed because one or more objects access this column
-
hello friends
i need your help for while..i created table run time
create table test (test_id int, test-name varchar(4))
then i add new column with some default value
alter table test add dept varchar(4) default 'A'
and now i want to drop this column but i am getting some error like
Server: Msg 5074, Level 16, State 1, Line 5
The object 'DF__tst__dept__4B8DDA46' is dependent on column 'dept'.
Server: Msg 4922, Level 16, State 1, Line 5
ALTER TABLE DROP COLUMN dept failed because one or more objects access this column.
But if i drop this constraint 'DF__dept__4B8DDA46' first and then drop column then i can do it but i want this at run time so how can i drop this constraint first and then this column...i want syntax....
please help me out....
Regards,
Papillon
-
hi
i got from one of the article from net
selectdb_name()as CONSTRAINT_CATALOG
,t_obj.name as TABLE_NAME
,user_name(c_obj.uid)as CONSTRAINT_SCHEMA
,c_obj.nameas CONSTRAINT_NAME
,col.nameas COLUMN_NAME
,col.colidas ORDINAL_POSITION
,com.textas DEFAULT_CLAUSE
fromsysobjectsc_obj
join syscommentscom on c_obj.id = com.id
join sysobjectst_obj on c_obj.parent_obj = t_obj.id
join sysconstraints con on c_obj.id= con.constid
join syscolumnscol on t_obj.id = col.id
and con.colid = col.colid
where
c_obj.uid= user_id()
and c_obj.xtype= 'D'
Any way thanks to all....
Regards,
Papillon
-
Hello,
I was able to locate the dependencies with this query, but how to delete / drop these links?
Thanks
-
In the initial post, notice the object name:
The object 'DF__tst__dept__4B8DDA46' is dependent on column 'dept'.
"DF" stands for "default value". You will have to include a step prior to dropping the column to remove the default value, as this is stored as a separate, dependent record. Then you can drop the original column.
-
Declare @v_constraintname varchar(max)
set @v_constraintname ='ALTER TABLE TableName DROP CONSTRAINT '
set @v_constraintname = @v_constraintname + (select c_obj.name as CONSTRAINT_NAME
from sysobjects c_obj
join syscomments com on c_obj.id = com.id
join sysobjects t_obj on c_obj.parent_obj = t_obj.id
join sysconstraints con on c_obj.id = con.constid
join syscolumns col on t_obj.id = col.id
and con.colid = col.colid
where
c_obj.uid = user_id()
and c_obj.xtype = 'D'
and t_obj.name='TableName' and col.name='ColumnName')
exec(@v_constraintname)
GO
In the 'TableName' value use the corresponding table Name
In the ColumnName value use the corresponding column Name to check it out....
Regards
S.Alagarsamy
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply