How to add a new column to all tables?

Databases
1

Hello all,

Is there a sql command to add a new column to all tables in a database?
Tim

2

No. You may need to loop over the table names and run an ALTER TABLE command for each.

3

I was hoping for a single SQL command, I didn’t want to write an app to do that but…

Thanks for your feedback Christian,
Tim

4

You want to add the same column (name) to all these tables?

5

Yes, same column name, type etc.

6

How many tables?

7

this is not a good database architecture practice

8

There’s 64 tables.
This is for a first attempt at creating a cloud app. Each user needs each of the 64 tables, but with the new column will be able to differentiate which records are for which user.

Tim

9

What I would do is open the data file in SQLiteManager (or any other browser)

  • perform an export to csv with the following SQL statement “SELECT name FROM “sqlite_sequence” ORDER BY “name” ASC;”
  • open the csv file
  • replace all the "t with ‘ALTER TABLE t’
  • replace the " at the end with ’ ADD COLUMN UserID VARCHAR(25);’
"tblCategory"
"tblContact"
"tblCurrencyRate"
"tblExpenses"

ALTER TABLE tblCategory"
ALTER TABLE tblContact"
ALTER TABLE tblCurrencyRate"
ALTER TABLE tblExpenses"

ALTER TABLE tblCategory ADD COLUMN UserID VARCHAR(25);
ALTER TABLE tblContact ADD COLUMN UserID VARCHAR(25);
ALTER TABLE tblCurrencyRate ADD COLUMN UserID VARCHAR(25);
ALTER TABLE tblExpenses ADD COLUMN UserID VARCHAR(25);
1 Like