Add columns to multiple tables in schema

Question

Hi,

I need to alter multiple tables in a schema, for all tables prefixed with something, for example:

ALTER TABLE "SCHEMA"."TABLE1"
ADD ( "COLUMN1"   CHARACTER(4)  NOT NULL    DEFAULT 'DATA',
    "COLUMN2"   VARCHAR(16)     NOT NULL    DEFAULT 'MORE_DATA',
);

I need this to iterate over multiple tables, like SCHEMA.table1, SCHEMA.table2... and so on. There is around 800 tables in the schema.

I was wondering if there is some kind of wildcard that I could use? I was looking at creating a cursor from the System tables, but was sure there must be an easier way.

Answer 1

Use the undocumented sp_MSforeachTable. You'll find lots of help on Google for that.

OP did not specify DB2 originally. My answer is thus redundant.

Answer 2

What I tend to do in those cases, if the DBMS doesn't provide an easy way to do it, is to simply write a script that will do it for me. Something like:

db2 "select tbname from sysibm.systables where schema = 'SCHEMA'" >tblist
# Edit tblist here to remove headers and such.
for t in $(cat tblist) ; do
    db2 "alter table SCHEMA.${t} add ..."
done

That's bash type format, you'll need to adapt for whatever scripting tool you use.

Answer 3

Thanks for everyone's input,

After creating what was (I thought) fancy procedure using a CURSOR and while loop to iterate through the tables, I decided since it is a once off to build an ALTER query for each of the tables from a select statement thus:

SELECT DISTINCT 'ALTER TABLE '
          || 'CTP0610'
          || '.'
          || name
          || ' ADD COLUMN SOURCE_SYSTEM_CODE CHAR(4) NOT NULL DEFAULT ''CCR'' '
          || ' ADD COLUMN RECORD_TYPE VARCHAR(16) NOT NULL DEFAULT ''INSERT'' '
          || ' ADD COLUMN COMMIT_TIMESTAMP TIMESTAMP NOT NULL DEFAULT '
          || ' ADD COLUMN EXTRACT_TIMESTAMP TIMESTAMP NOT NULL DEFAULT; '
    FROM sysibm.systables
    WHERE (NAME LIKE 'CCTL_%')
    OR (NAME LIKE 'CCX_%')
    OR (NAME LIKE 'CC_%');

I pasted the resultant queries into a query window and ran it, it turned out to be around 1500 tables. I guess sometimes the least elegant solution is good enough :-)