How to Change All SQL Columns of money and smallmoney into decimal?

Question

I have tried to do it using the answer to this question by modifying it to the following, but I'm getting an error on:

fetch next from maxcols into @schema, @table, @col, @dtype

Msg 8114, Level 16, State 5, Line 25
Error converting data type nvarchar to decimal.

Code:

declare @schema nvarchar(255)
declare @table nvarchar(255)
declare @col nvarchar(255)
declare @dtype decimal(19,8)
declare @sql nvarchar(max)

declare maxcols cursor for
    select
        c.TABLE_SCHEMA, c.TABLE_NAME,
        c.COLUMN_NAME, c.DATA_TYPE
    from
        INFORMATION_SCHEMA.COLUMNS c
    inner join 
        INFORMATION_SCHEMA.TABLES t on c.TABLE_CATALOG = t.TABLE_CATALOG
                                    and c.TABLE_SCHEMA = t.TABLE_SCHEMA
                                    and c.TABLE_NAME = t.TABLE_NAME
                                    and t.TABLE_TYPE = 'BASE TABLE'
    where
        c.DATA_TYPE like '%money'

open maxcols

fetch next from maxcols into @schema, @table, @col, @dtype

while @@FETCH_STATUS = 0
begin
    set @sql = 'alter table [' + @schema + '].[' + @table + 
        '] alter column [' + @col + '] ' + @dtype
    exec sp_executesql @sql

    fetch next from maxcols into @schema, @table, @col, @dtype
end

close maxcols
deallocate maxcols

Answer 1

A few changes:

Declare the @dtype variable of SYSNAME Type and assign a string of data type you want to use, in your case it would be a string 'decimal(19,8). You are declaring a variable of decimal(19,8) data type, this is not what you want.

You should really declare all the variable of SYSNAME type which will be used to hold SQL Server object names, Schema , Table, column names etc.

Also In your select for cursor you dont need to use the data type, it is being filtered out in the where clause for the cursor's select statement. So I have taken out the data type column from the select and from the Fetch next into line.

Also use QUOTENAME() function to add sqaure brackets around the object names.

declare  @table     SYSNAME
      ,  @col       SYSNAME 
      ,  @dtype     SYSNAME = 'decimal(19,8)'  --<-- Declare variable of sysname type
      ,  @sql       NVARCHAR(MAX) 
      ,  @schema    SYSNAME;

    declare maxcols cursor for
    select
        c.TABLE_SCHEMA,
        c.TABLE_NAME,
        c.COLUMN_NAME
    from
    INFORMATION_SCHEMA.COLUMNS c
    inner join INFORMATION_SCHEMA.TABLES t on
        c.TABLE_CATALOG = t.TABLE_CATALOG
        and c.TABLE_SCHEMA = t.TABLE_SCHEMA
        and c.TABLE_NAME = t.TABLE_NAME
        and t.TABLE_TYPE = 'BASE TABLE'
    where
        c.DATA_TYPE like '%money'

    open maxcols

    fetch next from maxcols into @schema, @table, @col

    while @@FETCH_STATUS = 0
    begin
        set @sql = N' alter table ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) 
                 + N' alter column ' + QUOTENAME(@col) + ' ' + @dtype

        exec sp_executesql @sql

        fetch next from maxcols into @schema, @table, @col
    end

    close maxcols
    deallocate maxcols