Post reply

smallmoney to money

  • March 15, 2007 at 10:55 am

    #175649

    I have fields in my database that have outgrown the smallmoney datatype -
     
    Is there code I can use to change the datatype form smallmoney to money whilst keeping the field description, default etc etc  and of course the data.
     
    I currently have
     
    ALTER TABLE [dbo].[Invoices] ALTER COLUMN [TotalFees] [money]
     
    but this fails because of a default.
     
    Thanks in advance.
     
    Adam

  • March 15, 2007 at 11:33 am

    #695306

    what is the default?

  • March 16, 2007 at 3:44 am

    #695406

    The default is 0 I think

  • March 16, 2007 at 6:43 am

    #695436

    You need to drop the default constraint first:

    ALTER TABLE [dbo].[Invoices] DROP CONSTRAINT constraint_name

    GO

    ALTER TABLE [dbo].[Invoices] ALTER COLUMN [TotalFees] [money]

    GO

    ALTER TABLE [dbo].[Invoices] ADD CONSTRAINT constraint_name DEFAULT (0) FOR [TotalFees]

    GO

    Hope that helps.

     

  • March 16, 2007 at 7:02 am

    #695440

    Thanks - unfortunately I don't know the contstraint name - the names have been system defined and are differant across dbs I want to run the code against. Can I do this without knowing the names?

  • March 16, 2007 at 7:24 am

    #695447

    This should help. This deletes a column and all constraints:

    http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1855

    You'll need to modify it to have it do an alter table/alter column command instead of deleting the column.

  • March 16, 2007 at 7:32 am

    #695450

    Cheers will give that a go.

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

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