Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I hope this question it's not so trivial as I couldn't find answer with Google

I have sqlite3 database with many tables which have same columns. I would like to list single column from all tables in database, but couldn't find a way to do so

From sqlite3 CLI client, something like:

sqlite3 -line my_db.db 'select my_column from *'

but, of course above does not work

share|improve this question
1  
Why do you have many tables with the same columns? Is it to partition your data? – Nick Chammas Nov 4 '11 at 16:38
It's a dictionary database, and each table name is starting word letter – zetah Nov 7 '11 at 14:20
1  
As Catcall said, then this is a design error. Everything should be in one table, perhaps with a computed column to indicate the starting letter of the word. There is no reason to have one table per letter unless you have billions of rows you need to partition somehow. You're basically stuck with using the solution Catcall gave you. – Nick Chammas Nov 7 '11 at 14:36

1 Answer

up vote 5 down vote accepted

You're looking for UNION and UNION ALL.

select my_column from table1
union all
select my_column from table2
union all
select my_column from ...

But needing to select one column from every table is likely the sign of a significant design error.

share|improve this answer
1  
+1 for the design error – a_horse_with_no_name Nov 4 '11 at 17:47
That would be rather long line to type :( Isn't there anything shorter? Database has 30 tables – zetah Nov 7 '11 at 14:21
1  
@zetah: Nothing shorter. Significant design errors often require a lot of typing to work around. You could write a program that takes the table names as input, and produces that SQL statement as output. I don't see how that would help much, though. – Mike Sherrill 'Catcall' Nov 7 '11 at 14:26
I dumped whole database and regex information I wanted, the other day. I think I'll redesign according Nick suggestion - all in one table with column indicating first letter. Cheers – zetah Nov 7 '11 at 14:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.