Suppose we have two similar tables:

table1: field1(int), field2(int), field3(int); table2: field1(int), field2(varchar), field3(int);

Can I write a SQL query that detects and shows the difference in fields ( not records ) between these two tables?

  • What is the database server? - Zufir
  • at the moment PG - perfect

1 answer 1

Use the request to information_schema.Columns :

 select t1.column_name, t1.data_type as data_type1, t2.data_type as data_type2 from information_schema.Columns t1 join information_schema.Columns t2 on t1.column_name=t2.column_name where t1.table_name='table1' and t2.table_name='table2'

There are options with different types of join and preliminary preparation of the key, according to which we will compare field types. But in general - look there.

Will work in PG, MS SQL, MySql. Will not work in Oracle , SQLite , use the appropriate mechanisms there.

  • beautiful, thank you very much - perfect