alter drop

Question

hi,

i am getting error :

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'default'.

alter

alter

column  [MiddleName]drop  default

table[dbo].[Person]

Answer 1

Check this

http://msdn.microsoft.com/en-us/library/ms190273.aspx

---

Many Thanks & Best Regards, Hua Min

Answer 2

Try This

Alter Table dbo.person

Alter  column Middlename drop default

---

Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Miss Never Giveup .

Answer 3

If you want to drop a constraint you need to do ALTER TABLE <table name> DROP CONSTRAINT <constraint name>.

Of course, you know the table name (dbo.Person), but you need to find the constraint name.  To do that, run

select d.name
from sys.default_constraints d
inner join sys.objects o on d.parent_object_id = o.object_id
inner join sys.columns c on c.object_id = d.parent_object_id and c.column_id = d.parent_column_id
where o.name = 'Person' and SCHEMA_NAME(o.schema_id) = 'dbo' 
  and d.type_desc = 'DEFAULT_CONSTRAINT' and c.name = 'MiddleName'

That will tell you the name of the default constraint for that column.  It will probably look something like

DF__Person__MiddleName__7C104AB9

Once you know the constraint name, then you can do (if, for example, the above was the constraint name)

Alter Table dbo.Person Drop Constraint DF__Person__MiddleName__7C104AB9

Tom

Answer 4

Hi TSQL_New,

Please check the given sample code at http://www.kodyaz.com/articles/how-to-drop-default-constraint-without-name-sql-server.aspx

which will help you to find the default constraint name and drop it using sys.default_constraints system view and ALTER TABLE...DROP CONSTRAINT command

---

SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

Answer 5

Hi,

This error occurs when the syntax of the statement is not correct.

Syntax :ALTER TABLE table_name  DROP column_name

ALTER TABLE info
 DROP COLUMN Remarks

---

Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

Answer 6

Coincidently i had to do the same thing few mins back, this is working solution

declare @default sysname, @sql nvarchar(max)
-- get name of default constraint
select @default = name 
from sys.default_constraints 
where parent_object_id = object_id('Schema.tablename')
AND type = 'D'
AND parent_column_id = (
    select column_id 
	from sys.columns 
    where object_id = object_id('Schema.tablename')
	and name = 'ColumnName'
)
set @sql = N'alter table Schema.tablename drop constraint ' + @default
exec sp_executesql @sql
ALTER TABLE Schema.tablename
DROP COLUMN ColumnName

---

Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Miss Never Giveup .

Answer 7

i want to drop default constraint onmy column

Answer 8

ALTER TABLE <Table-Name>
    DROP CONSTRAINT <Constrant-Name>
GO

Try above code Plz.....

---

Muhammad Azeem

Answer 9

Run this , change table name and column name and this will drop contraint

declare @default sysname, @sql nvarchar(max)
-- get name of default constraint
select @default = name
from sys.default_constraints
where parent_object_id = object_id('Schema.tablename')
AND type = 'D' AND parent_column_id = (
    select column_id
from sys.columns
    where object_id = object_id('Schema.tablename')
and name = 'ColumnName'
)
set @sql = N'alter table Schema.tablename drop constraint ' + @default
exec sp_executesql @sql

---

Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Miss Never Giveup .