Replacing for loop by sql

Question

I have SQL for example

show tables from mydb;

It shows the list of table

|table1|
|table2|
|table3|

Then,I use sql sentence for each table. such as "show full columns from table1 ;"

+----------+--------+-----------+------+-----+---------+----------------+---------------------------------+---------+
| Field    | Type   | Collation | Null | Key | Default | Extra          | Privileges                      | Comment |
+----------+--------+-----------+------+-----+---------+----------------+---------------------------------+---------+
| id       | bigint | NULL      | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| user_id  | bigint | NULL      | NO   | MUL | NULL    |                | select,insert,update,references |         |
| group_id | int    | NULL      | NO   | MUL | NULL    |                | select,insert,update,references |         |
+----------+--------+-----------+------+-----+---------+----------------+---------------------------------+---------+

So in this case I can use programming language such as .(this is not correct code just showing the flow)

tables = "show tables from mydb;"
for t in tables:
    cmd.execute("show full columns from {t} ;")

However is it possible to do this in sql only?

Please specify the DB server - MS SQL Server, MySQL, PostgreSQL, ... Probably the INFORMATION_SCHEMA.COLUMNS approach, suggested by @gotqn will work in most cases. — Mike Mozhaev
– Mike Mozhaev, Commented Jan 30, 2023 at 6:42

s.soltanzadeh · Accepted Answer · 2023-01-30 15:03:29Z

1

If you are using MySQL you can use the system view - INFORMATION_SCHEMA.

It contains table name and column name (and other details). No loop is require and you can easily filter by other information, too.

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS

If you are using Microsoft SQL Server, you can use the above command

edited Jan 30, 2023 at 15:03

s.soltanzadeh

1264 bronze badges

answered Jan 30, 2023 at 6:33

gotqn

44k47 gold badges168 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Replacing for loop by sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related