user4402742 user4402742 - 29 days ago 4
SQL Question

SQL Server -- updating the `sys.*` tables and not just reading them

In an attempt to the query

UPDATE sys.columns
SET user_type_id = 106
WHERE object_id in (select object_id from sys.objects where type = 'U') and user_type_id = 108


I'm getting the error:


Msg 259, Level 16, State 1, Line 1

Ad hoc updates to system catalogs are not allowed.


Is there a way to get around this? In this case, I'm looking to change the types of all decimal fields of all the tables in the database.

Can do this "externally"-- without direct tampering with
sys.*
tables (haven't yet pinned down how-to though), but I'm looking to know whether I can update the
sys.*
tables -- and if so, which ones, when/how?

// =========================

EDIT:

would i be able to get any "deeper" than
alter table...
if i had full privileges for db access?
not sure what kind of privileges i have now, but would look into it.

Answer

These tables are informational only. I want to make this clear: the sys.* and 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 ALTER TABLE.

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).

*For the 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.