CODEWITHSUNDEEP
sqloracle-database
AlexBay
AlexBay

Reputation: 1313

Multi-column comment

I know that I can comment a column with COMMENT ON COLUMN table.column IS 'commentString', but is there a way to add the same comment to more than one column in one statement?

For example, I need to add the comment "User Data" to columns NAME and BIRTHDATE and I'd like to understand if it's possible to do it in one statement, instead of repeating COMMENT ON COLUMN x.y IS 'User Data' two times.

Upvotes: 3

Views: 717

Answers (1)

Ilia Maskov
Ilia Maskov

Reputation: 1898

It is impossible in straight way, but you can do it with dynamic SQL in PL\SQL block

BEGIN
    FOR i IN (SELECT t.owner || '.' || t.table_name || '.' || t.column_name col
                FROM all_tab_cols t
               WHERE t.owner = 'OWNER' AND t.table_name = 'TABLE_NAME' AND
                     t.column_name IN ('COL1', 'COL2'))
    LOOP
        EXECUTE IMMEDIATE 'COMMENT ON COLUMN ' || i.col ||
                          ' IS ''PLACE COMMENT HERE''';
    END LOOP;
END;

Upvotes: 2

Related Questions