Converting data_type of many fields from bit to tinyint

Question

Is the following query an acceptable way to convert the fields' data_types?

UPDATE INFORMATION_SCHEMA.COLUMNS 
SET `COLUMNS`.`DATA_TYPE` = 'tinyint'
WHERE
`COLUMNS`.`TABLE_SCHEMA` = 'BAR' 
AND `COLUMNS`.`TABLE_NAME` = 'FOO'
AND `COLUMNS`.`DATA_TYPE` = 'bit'

Should I be concerned about data integrity or will data stored as bit move to tinyint without issue?

Well, if there is no issue with data integrity I could change all my tables this well in one swell foop... — jerrygarciuh, May 11 '12 at 04:06

score 1 · Answer 1 · edited May 23 '17 at 10:30

1

This won't do. Operations UPDATE, DELETE, INSERT are not allowed on the INFORMATION_SCHEMA database.

Here's why.

edited May 23 '17 at 10:30

Community

1
1

answered Dec 16 '16 at 13:59

Danil Sorokin

11
2

score 1 · Accepted Answer · answered Dec 16 '16 at 14:16

You can't directly modify the metadata tables (they're actually views but the distinction's not important for this case) but you can use them to generate your alter table statements, something like this:

SELECT 'ALTER TABLE ' + TABLE_SCHEMA + '.' + TABLE_NAME + ' ALTER COLUMN ' + COLUMN_NAME + ' TINYINT' + CASE WHEN IS_NULLABLE = 'YES' THEN ' NULL;' ELSE ' NOT NULL;' END
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE `COLUMNS`.`TABLE_SCHEMA` = 'BAR' 
AND `COLUMNS`.`TABLE_NAME` = 'FOO'
AND `COLUMNS`.`DATA_TYPE` = 'bit'

Converting data_type of many fields from bit to tinyint

2 Answers2