In an attempt to the query
SET user_type_id = 106
WHERE object_id in (select object_id from sys.objects where type = 'U') and user_type_id = 108
Msg 259, Level 16, State 1, Line 1
Ad hoc updates to system catalogs are not allowed.
These tables are informational only. I want to make this clear: the
INFORMATION_SCHEMA.* views exist to provide schema information from the database engine in a useful format. They do not represent the actual schema of the database*, and modifying them is thus impossible. The only way to change your schema is to use DDL (Data Definition Language) statements, such as
In your case, you can use a cursor to iterate through all columns with the wrong type, generate SQL statements to correct that, and execute them dynamically. Here's a skeleton of how that would look:
DECLARE column_cursor CURSOR FOR SELECT schemas.name AS schema_name, objects.name AS table_name, columns.name AS column_name FROM sys.columns JOIN sys.objects ON objects.object_id = columns.object_id JOIN sys.schemas ON schemas.schema_id = objects.schema_id WHERE objects.type = 'U' AND columns.user_type_id = 108 DECLARE @schema_name VARCHAR(255) DECLARE @table_name VARCHAR(255) DECLARE @column_name VARCHAR(255) OPEN column_cursor FETCH NEXT FROM column_cursor INTO @schema_name, @table_name, @column_name WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @sql VARCHAR(MAX) -- TODO: modify to change to the actual type, scale and precision you want; also you may need to adjust for NOT NULL constraints, default constraints and foreign keys (all exercises for the reader) SET @sql = 'ALTER TABLE ' + QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name) + ' CHANGE COLUMN ' + QUOTENAME(@column_name) + ' DECIMAL(12, 2)' EXEC(@sql) FETCH NEXT FROM column_cursor INTO @schema_name, @table_name, @column_name END CLOSE column_cursor DEALLOCATE column_cursor
Because of the potential increase in complexity for dealing with constraints and keys, I'd recommend either updating the columns manually, building the
ALTER TABLE statements manually, dumping your schema to script, updating that and recreating the tables and objects, or looking for a 3rd party tool that does this kind of thing (I don't know of any).
sys.* views, at least, it's possible that they closely represent the underlying data structures, though I think there's still some abstraction.
INFORMATION_SCHEMA is ANSI-defined, so it is unlikely to match the internal structures of any database system out there.