CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Michelle Daniel
Michelle Daniel

Reputation: 165

Is there a way to alter many tables to add default values to a common column name?

We have a set of 45 tables which carry a common column {variety}. The need is to set all such columns with a default value {comedy}.

The ALTER TABLE (SCHEMA.TABLE_NAME) MODIFY(VARIETY DEFAULT 'COMEDY')

Will get it done, but I am wondering if there is a way to create a sql script in Oracle 11g that will change all tables within the schema which have a common coloumn name to the common default value.

Upvotes: 1

Views: 686

Answers (2)

Meeran Mohideen
Meeran Mohideen

Reputation: 81

The alter table statement can be written as following, using alternate quoting mechanism.

'alter table ' || x.table_name || q'[ modify (variety default 'COMEDY')]'

Upvotes: 0

Sebas
Sebas

Reputation: 21522

DECLARE
    cnt NUMBER;
BEGIN
    FOR x IN (
        SELECT DISTINCT t.table_name 
        FROM user_tables t
            INNER JOIN user_tab_columns c ON c.table_name = t.table_name
    ) LOOP
        EXECUTE IMMEDIATE 'ALTER TABLE (SCHEMA.' || x.table_name || ') MODIFY(VARIETY DEFAULT ''COMEDY'')';
    END LOOP;
END;

Upvotes: 2

Related Questions