Mount an ALTER TABLE script

Asked

Viewed 105 times

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 assemble a script with many ALTER TABLE <TABELA> ALTER COLUMN REV smallint;.

How can I generate this script ?

sql sql-server

1 answer

7


You can use the CONCAT, generating a query for each 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

The CONCAT is available in 2012, in previous versions you can do something by concatenating like this:

SELECT
    ('ALTER TABLE ' + table_Name + ' ALTER COLUMN REV smallint;' )
  • 1

    Show, thank you very much.

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.