none
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]
    Thursday, November 29, 2012 2:38 PM

All replies

  • Check this

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


    Many Thanks & Best Regards, Hua Min

    Thursday, November 29, 2012 2:45 PM
  • 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 .

    • Proposed as answer by Muhammad-Azeem Thursday, November 29, 2012 7:10 PM
    • Unproposed as answer by Muhammad-Azeem Thursday, November 29, 2012 7:11 PM
    Thursday, November 29, 2012 2:56 PM
  • 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

    Thursday, November 29, 2012 3:02 PM
  • 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

    Thursday, November 29, 2012 3:05 PM
  • 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/

    Thursday, November 29, 2012 3:24 PM
  • 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 .

    Thursday, November 29, 2012 5:07 PM
  • i want to drop default constraint onmy column
    Thursday, November 29, 2012 6:36 PM
  • ALTER TABLE <Table-Name>
        DROP CONSTRAINT <Constrant-Name>
    GO

    Try above code Plz.....

    Muhammad Azeem

    Thursday, November 29, 2012 7:17 PM
  • 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 .

    Thursday, November 29, 2012 9:21 PM