Cast Varchar at smallmoney

I have about 100 columns in my table, 50 of which need to be changed to (smallmoney, null) format. Currently they are all (varchar(3), null).

How do I do that with the columns I want? Is there a quick way? Let's pretend I have 5 columns:

col1 (varchar(3), null)
col2 (varchar(3), null)
col3 (varchar(3), null)
col4 (varchar(3), null)
col5开发者_如何学Go (varchar(3), null)

how do I make them look like this:

col1 (smallmoney, null)
col2 (smallmoney, null)
col3 (smallmoney, null)
col4 (varchar(3), null)
col5 (varchar(3), null)

You can programmatically create the ALTER script, and then execute it. I just chopped this out, you'll need to validate the syntax:

SELECT
    'ALTER TABLE "' + TABLE_NAME + '" ALTER COLUMN "' + COLUMN_NAME + '" SMALLMONEY'
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
        TABLE_NAME = 'MyTable'
    AND COLUMN_NAME LIKE 'Pattern%'

Give this a shot, but make a backup of the table first... no idea how the automatic conversion of that data will go.

alter table badlyDesignedTable alter column col1 smallmoney, col2 smallmoney, col3 smallmoney

edit: changed syntax

You can query the system tables or ANSI views for the columns in question and generate the ALTER table statements. This

select SQL = 'alter table'
           + ' ' + TABLE_SCHEMA
           +       '.'
           +       TABLE_NAME
           + ' ' + 'alter column'
           + ' ' + COLUMN_NAME
           + ' ' + 'smallmoney'
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo'
  and TABLE_NAME   = 'MyView'
order by ORDINAL_POSITION

will generate an alter table statement for every column in the table. You'll need to either filter it in the where clause or past the results into a text editor and remove the ones you don't want.

Read up on ALTER TABLE ALTER COLUMN though...modifying a column with alter column comes with constraints and limitations, especially if it is indexed. alter table will fail if any column can't be converted to the target data type. For varchar->smallmoney conversion, you'll fail if any row contains anything that doesn't look like a string literal of the appropriate type. If it won't convert with CONVERT(smallmoney,alter table will fail. it the column contains nil ('') or whitespace (' '), the conversion will most likely succeed (in the case of a smallmoney target, I suspect you'll get 0.0000 as a result).

Bear in mind that multiple values may wind up converted to the same value in the target datatype. This can hose indexes.

If you're trying to convert from a nullable column to a non-nullable column, you'll need to first ensure that every row has a non-null value first. Otherwise the conversion will fail.

Good luck.