How acceptable is it to use DB metadata in your queries?

[dennis-fedco]

I have a feature in my Web Application where I make a copy of an business object.  Business object is stored in the database.  The way I make a copy is a little generic and I wanted to check if I am doing it right. 

 

Namely, I use Database metadata to help me instead of using actual column names from a table.  I have functionality in my DB where I use SQL statements like so:

SELECT COLUMN_NAME
FROM information_schema.COLUMNS
WHERE
TABLE_SCHEMA = 'db_name' AND
TABLE_NAME = 'table_name' AND
COLUMN_NAME NOT IN ('id', 'db_table_foreign_key');

it essentially selects column names from a particular table belonging to the business object, minus the keys.  All I need to do to copy another table is to change the DB and table names in the above SQL.

 

Later I use another SQL to do the actual new record creation:

INSERT INTO db_name.table_name SET ... -- uses PHP code to essentially copy over things, like:
   {'column_name' = $value, ...} -- $value is from another SQL (not shown) that loads up values to copy
   foreign_key = $new_foreign_key;

Where I use PHP code that uses variables from the first SQL (and actual values from another SQL which is not shown) to essentially create a new table with values copied over from old table to new table, creates new keys, and foreign keys.

 

My question is about "am I doing this right"?  I have many tables and in a way, taking a cheap way out, doing this same generic code basically to copy over several tables, instead of copying every table by using actual SQL with actual table names for each separate table. 

 

I am concerned that going to information_schema.COLUMNS is problematic, even though it saves me lots of work when tables change colum names/ new names are added, etc.

 

I'll appreciate any insight and experience on if my approach is reasonably industry-practied...

 

 

[requinix]

My first reaction is "Why are you creating so many tables?", followed by "Why doesn't your application know the table structures already?"

[dennis-fedco]

my answer .... 6 tables.  Not that many.  Why - to store data related to my application.

 

I am not sure what you mean by my application already knowing the table structure.

It is using the tables, so it is aware of them.

[requinix]

I was saying that because of:

 

Namely, I use Database metadata to help me instead of using actual column names from a table.

(your application doesn't know the columns in the table so it has to query the metadata for them)

 

All I need to do to copy another table is to change the DB and table names in the above SQL.

(you say you're copying tables)

 

Where I use PHP code that uses variables from the first SQL (and actual values from another SQL which is not shown) to essentially create a new table with values copied over from old table to new table, creates new keys, and foreign keys.

(creating tables)

 

I have many tables and in a way, taking a cheap way out, doing this same generic code basically to copy over several tables, instead of copying every table by using actual SQL with actual table names for each separate table.

("many tables" and you're copying them around)

 

I am concerned that going to information_schema.COLUMNS is problematic, even though it saves me lots of work when tables change colum names/ new names are added, etc.

(tables are changing schema on a regular basis)