5

I want to select column names but I don't know the table structure ahead of time and it may change so I can't just hard code the select statement with column names. I also do NOT want to select every column. Is there and easy way to do this?

My thoughts are it's some kind of combination of these two queries but my SQL is not that good.

SHOW COLUMNS FROM table_name;
SELECT * FROM table_name;

I tried using a sub select but it didn't work. Nothing seems to happen, I don't get an error I just get no results

SELECT (SELECT column_name 
        FROM information_schema.columns 
        WHERE table_name ='table_name') 
FROM table_name;

Maybe I need to do a join?.. Anyway any help would be great, thanks

Cade Roux
  • 88,164
  • 40
  • 182
  • 265
TheSnooker
  • 935
  • 2
  • 11
  • 24
  • 4
    It seems strange that the table structure is constantly changing, that sounds like a design flaw. Can you post your current structure or provide more context on your tables? – Taryn Mar 19 '13 at 18:35
  • It's not constantly changing. However the final structure of the tables is not set in stone yet, and so I am trying to make a system that is smart enough to adapt to whatever changes might be in the future. I don't know the column names ahead of time. I could do this in PHP with 2 separate queries but I though I could also do it at the Database layer too. I was thinking I could get the column names, then use that data to query the table basically build a dynamic select statement. – TheSnooker Mar 19 '13 at 18:48
  • You can do this with dynamic SQL (it will effectively be two queries), but if you don't want all the columns, you will have to code into your query selecting the columns from the metadata what the criteria are for including a column (and any particular ordering you want). But you can't turn the results of a query on the metadata directly into a select list for the selection query. – Cade Roux Mar 19 '13 at 19:13
  • @Cade Roux My criteria for column names is a prefix on the column name. I was thinking I could either exlude or include based on that. "SHOW COLUMNS FROM table_name WHERE Field LIKE %prefix%" – TheSnooker Mar 20 '13 at 16:55

1 Answers1

2

Try this SQLFiddle:

CREATE TABLE atable (
  prefix1 VARCHAR(10)
  ,prefix2 VARCHAR(10)
  ,notprefix3 INT
  ,notprefix4 INT
);

INSERT INTO atable VALUES ('qwer qwer', 'qwerqwer', 1, 1);
INSERT INTO atable VALUES ('qwer qwer', 'asdfaasd', 1, 1);
INSERT INTO atable VALUES ('qwer qwer', 'qrt vbb', 1, 1);
INSERT INTO atable VALUES ('qwer qwer', 'sdfg sdg', 1, 1);

SELECT CONCAT('SELECT ', GROUP_CONCAT(c.COLUMN_NAME), ' FROM atable;')
INTO @query
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = 'atable'
  AND c.COLUMN_NAME LIKE 'prefix%'
ORDER BY c.ORDINAL_POSITION;

PREPARE stmt FROM @query;

EXECUTE stmt;

Some issues:

You will likely want some kind of ORDER BY on your result set.

There's a limit to what you can do in terms of joins and things.

You move validation to runtime where it's more likely to be missed by testing.

You are hoping to be able to handle schema changes easily. This technique will only handle schema changes of a certain type you can foresee, and others you will probably have to change this code anyway.

Cade Roux
  • 88,164
  • 40
  • 182
  • 265
  • Yeah, I see what you mean. I had hopes that I could create a sort of intelligent query system that would only grab certain columns based on a set of rules I could define and change. The more I look into this approach the more I realize that it would probably create more complexity then it's worth. I never thought about using CONCAT though, clever. – TheSnooker Mar 21 '13 at 14:06
  • @TheSnooker code generation and dynamic SQL has a place in making easier to maintain systems. In fact, I have a whole presentation on using metadata for code generation and database code health monitoring. But I don't think it has a special better place in future proofing this particular scenario. – Cade Roux Mar 21 '13 at 15:18