Alter table Data Type

• Richard Oths

  Old Hand

  Points: 320

  More actions

  August 15, 2007 at 11:34 am

  #118209

  Hello,

  Is there a way to programatically change all fields in all tables in a db from data type float to decimal?

  Thank you

• Lowell

  SSC Guru

  Points: 323518

  More actions

  August 15, 2007 at 12:38 pm

  #727148

  the way to do it is with the ALTER TABLE ALTER COLUMN command:

  example:

  create table test (testid int,testcol float);

  exec sp_help test;

  alter table test alter column testcol decimal;

  exec sp_help test;

  drop table test;

  now to do it to every FLOAT column in your database, you have to find them and run the results of this query after reviewing them for accuracy:

  select object_name(id) As TableName,

  name as Columnname,

  TYPE_NAME(xtype) AS datatype,

  'ALTER TABLE ' + object_name(id) + ' ALTER COLUMN ' + name + ' decimal' AS SQL

  from syscolumns

  where TYPE_NAME(xtype)='float'

   

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Desh Gupta

  SSC Journeyman

  Points: 76

  More actions

  August 16, 2007 at 7:49 am

  #727326

  Hi

  i think this will help you

  check the system table (information_schema.tables) and apply alter table commend

• Richard Oths

  Old Hand

  Points: 320

  More actions

  August 16, 2007 at 6:35 pm

  #727520

  Thank you both for the very informative descriptions!