Assemble an ALTER TABLE script

Navigation
1

I have this select that returns me the name of the tables that interest me: 

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE '%_AUD' AND COLUMN_NAME = 'REVTYPE' GROUP BY TABLE_NAME;

From this result I would like to put together a script with many ALTER TABLE <TABELA> ALTER COLUMN REV smallint; .

How can I create this script?

    
asked by anonymous 11.04.2018 / 18:31

1 answer

7

You can use CONCAT , generating a query for every table_name : 

SELECT
   CONCAT( 'ALTER TABLE ', table_name, ' ALTER COLUMN REV smallint;') AS linha
FROM
   information_schema.columns
WHERE
   table_name LIKE '%_AUD' AND column_name = 'REVTYPE'
GROUP BY 
   table_name

CONCAT is available in 2012, in earlier versions you can do something concatenating like this: 

SELECT
    ('ALTER TABLE ' + table_Name + ' ALTER COLUMN REV smallint;' )
    
11.04.2018 / 18:33
Check if window load is false Program that warns a secret number using bisection search