July 13, 2013 at 12:05 pm
Thanks you very much for your time as its greatly appreciated. The situation seems to be simple as it should be but for some reason this situation is throwing me some massive headaches…
I have a table and that table has a column definition needs to be altered from numeric to int..this table also has 41 constraints on it as I ran to show me all the constraints. The server is set so I should be able to make the changes as I went into the object explorer ->tools->options->designer->table and databse design and uncheck the proper box.
SELECT * FROM sys.objects
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%bl%'
Then taking all the constraints that came back..next I ran:
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[DF__bl__count_em__0275918A]') AND parent_object_id = OBJECT_ID(N'[dbo].[bl]'))ALTER TABLE [dbo].[bl] DROP CONSTRAINT [DF__bl__count_em__0275918A]
GO
Ran this on all constraints and then tried to run:
ALTER TABLE [dbo].[bl]--NOCHECK CONSTRAINT all
ALTER column [count_em] int not null;
Yet this still return the error :
Msg 5074, Level 16, State 1, Line 1
The object 'DF__bl__count_em__0275918A' is dependent on column 'count_em'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN count_em failed because one or more objects access this column.
Any help or ideas are greatly appreciated. THANK YOU
DHeath
July 13, 2013 at 12:54 pm
Edit again...
That looks like a default constraint. As such you won't find it in the DMV that lists foreign key constraints.
To alter the column you need to drop all constraints, primary, unique, default and foreign key, as well as all indexes.
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
July 13, 2013 at 2:49 pm
Thank you everyone for looking and taking the time to read thru....greatly apprecatied
Was able to get this fixed with the use of sp_unbinddefault ....as a surprise to me didnt know that existed and kinda thought that drop constraint would do the same things.
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'DF__bl__count_em__0275918A'
AND type = 'D')
BEGIN
EXEC sp_unbindefault '<tablename>' DROP DEFAULT <constraint_name>
END
Dheath
DHeath
July 13, 2013 at 4:18 pm
sp_unbindefault should not work, nor does it when I test.
ALTER TABLE DROP CONSTRAINT works, but you should check sys.default_constraints, and not sys.foreign keys.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply