<p>I'm trying to use a select statement to get all of the columns from a certain MySQL table except one. Is there a simple way to do this?</p>

<p>EDIT: There are 53 columns in this table (NOT MY DESIGN)</p>

[{'answer_id': 9123, 'author': 'Thomas Owens', 'author_id': 572, 'author_profile': 'https://Stackoverflow.com/users/572', 'pm_score': 5, 'selected': False, 'text': "<p>To the best of my knowledge, there isn't. You can do something like:</p>\n\n<pre><code>SELECT col1, col2, col3, col4 FROM tbl\n</code></pre>\n\n<p>and manually choose the columns you want. However, if you want a lot of columns, then you might just want to do a:</p>\n\n<pre><code>SELECT * FROM tbl \n</code></pre>\n\n<p>and just ignore what you don't want.</p>\n\n<p>In your particular case, I would suggest:</p>\n\n<pre><code>SELECT * FROM tbl\n</code></pre>\n\n<p>unless you only want a few columns. If you only want four columns, then:</p>\n\n<pre><code>SELECT col3, col6, col45, col 52 FROM tbl\n</code></pre>\n\n<p>would be fine, but if you want 50 columns, then any code that makes the query would become (too?) difficult to read.</p>\n"}, {'answer_id': 9124, 'author': 'Mike Stone', 'author_id': 122, 'author_profile': 'https://Stackoverflow.com/users/122', 'pm_score': 5, 'selected': False, 'text': '<p>You can do:</p>\n\n<pre><code>SELECT column1, column2, column4 FROM table WHERE whatever\n</code></pre>\n\n<p>without getting column3, though perhaps you were looking for a more general solution?</p>\n'}, {'answer_id': 9131, 'author': 'Stacey Richards', 'author_id': 1142, 'author_profile': 'https://Stackoverflow.com/users/1142', 'pm_score': 4, 'selected': False, 'text': "<p>If the column that you didn't want to select had a massive amount of data in it, and you didn't want to include it due to speed issues and you select the other columns often, I would suggest that you create a new table with the one field that you don't usually select with a key to the original table and remove the field from the original table. Join the tables when that extra field is actually required.</p>\n"}, {'answer_id': 9135, 'author': 'jammycakes', 'author_id': 886, 'author_profile': 'https://Stackoverflow.com/users/886', 'pm_score': 3, 'selected': False, 'text': '<p>You could use <a href="http://dev.mysql.com/doc/refman/5.0/en/describe.html" rel="noreferrer">DESCRIBE my_table</a> and use the results of that to generate the SELECT statement dynamically.</p>\n'}, {'answer_id': 9146, 'author': 'Brian Childress', 'author_id': 721, 'author_profile': 'https://Stackoverflow.com/users/721', 'pm_score': 6, 'selected': False, 'text': '<p>Would a View work better in this case? </p>\n\n<pre><code>CREATE VIEW vwTable\nas  \nSELECT  \n    col1  \n    , col2  \n    , col3  \n    , col..  \n    , col53  \nFROM table\n</code></pre>\n'}, {'answer_id': 9147, 'author': 'nlucaroni', 'author_id': 157, 'author_profile': 'https://Stackoverflow.com/users/157', 'pm_score': 2, 'selected': False, 'text': "<p>I agree that it isn't sufficient to <code>Select *</code>, if that one you don't need, as mentioned elsewhere, is a BLOB, you don't want to have that overhead creep in. </p>\n\n<p>I would create a <strong>view</strong> with the required data, then you can <code>Select *</code> in comfort --if the database software supports them. Else, put the huge data in another table.</p>\n"}, {'answer_id': 9153, 'author': 'Gareth Simpson', 'author_id': 147, 'author_profile': 'https://Stackoverflow.com/users/147', 'pm_score': 1, 'selected': False, 'text': "<p>If it's always the same one column, then you can create a view that doesn't have it in it.</p>\n\n<p>Otherwise, no I don't think so.</p>\n"}, {'answer_id': 9156, 'author': 'mbillard', 'author_id': 810, 'author_profile': 'https://Stackoverflow.com/users/810', 'pm_score': 2, 'selected': False, 'text': "<p>It is good practice to specify the columns that you are querying even if you query all the columns.</p>\n\n<p>So I would suggest you write the name of each column in the statement (excluding the one you don't want).</p>\n\n<pre><code>SELECT\n    col1\n    , col2\n    , col3\n    , col..\n    , col53\n\nFROM table\n</code></pre>\n"}, {'answer_id': 9217, 'author': 'icco', 'author_id': 1063, 'author_profile': 'https://Stackoverflow.com/users/1063', 'pm_score': 2, 'selected': False, 'text': '<p>At first I thought you could use regular expressions, but as I\'ve been reading the <a href="http://dev.mysql.com/doc/refman/5.0/en/select.html" rel="nofollow noreferrer">MYSQL docs</a> it seems you can\'t. If I were you I would use another language (such as PHP) to generate a list of columns you want to get, store it as a string and then use that to generate the SQL. </p>\n'}, {'answer_id': 9222, 'author': 'OJ.', 'author_id': 611, 'author_profile': 'https://Stackoverflow.com/users/611', 'pm_score': 2, 'selected': False, 'text': "<p>While I agree with Thomas' answer (+1 ;)), I'd like to add the caveat that I'll assume the column that you <em>don't</em> want contains hardly any data. If it contains enormous amounts of text, xml or binary blobs, then take the time to select each column individually. Your performance will suffer otherwise. Cheers!</p>\n"}, {'answer_id': 1375831, 'author': 'rplevy', 'author_id': 163815, 'author_profile': 'https://Stackoverflow.com/users/163815', 'pm_score': 2, 'selected': False, 'text': '<p>You can use SQL to generate SQL if you like and evaluate the SQL it produces.  This is a general solution as it extracts the column names from the information schema. Here is an example from the Unix command line. </p>\n\n<p>Substituting</p>\n\n<ul>\n<li>MYSQL with your mysql command</li>\n<li>TABLE  with the table name</li>\n<li>EXCLUDEDFIELD with excluded field name</li>\n</ul>\n\n\n\n<pre><code>echo $(echo \'select concat("select ", group_concat(column_name) , " from TABLE") from information_schema.columns where table_name="TABLE" and column_name != "EXCLUDEDFIELD" group by "t"\' | MYSQL | tail -n 1) | MYSQL\n</code></pre>\n\n<p>You will really only need to extract the column names in this way only once to construct the column list excluded that column, and then just use the query you have constructed.</p>\n\n<p>So something like:</p>\n\n<pre><code>column_list=$(echo \'select group_concat(column_name) from information_schema.columns where table_name="TABLE" and column_name != "EXCLUDEDFIELD" group by "t"\' | MYSQL | tail -n 1)\n</code></pre>\n\n<p>Now you can reuse the <code>$column_list</code> string in queries you construct.</p>\n'}, {'answer_id': 1495476, 'author': 'Mahomedalid', 'author_id': 143393, 'author_profile': 'https://Stackoverflow.com/users/143393', 'pm_score': 9, 'selected': True, 'text': "<p>Actually there is a way, you need to have permissions of course for doing this ...</p>\n\n<pre><code>SET @sql = CONCAT('SELECT ', (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), '&lt;columns_to_omit&gt;,', '') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '&lt;table&gt;' AND TABLE_SCHEMA = '&lt;database&gt;'), ' FROM &lt;table&gt;');\n\nPREPARE stmt1 FROM @sql;\nEXECUTE stmt1;\n</code></pre>\n\n<p>Replacing <code>&lt;table&gt;, &lt;database&gt; and &lt;columns_to_omit&gt;</code></p>\n"}, {'answer_id': 2019450, 'author': 'Soth', 'author_id': 182619, 'author_profile': 'https://Stackoverflow.com/users/182619', 'pm_score': 2, 'selected': False, 'text': '<p>Just do</p>\n\n<pre><code>SELECT * FROM table WHERE whatever\n</code></pre>\n\n<p>Then drop the column in you favourite programming language: php</p>\n\n<pre><code>while (($data = mysql_fetch_array($result, MYSQL_ASSOC)) !== FALSE) {\n   unset($data["id"]);\n   foreach ($data as $k =&gt; $v) { \n      echo"$v,";\n   }      \n}\n</code></pre>\n'}, {'answer_id': 2322117, 'author': 'Nathan A', 'author_id': 279884, 'author_profile': 'https://Stackoverflow.com/users/279884', 'pm_score': 3, 'selected': False, 'text': '<p>Yes, though it can be high I/O depending on the table here is a workaround I found for it.</p>\n\n<pre><code>SELECT *\nINTO #temp\nFROM table\n\nALTER TABLE #temp DROP COlUMN column_name\n\nSELECT *\nFROM #temp\n</code></pre>\n'}, {'answer_id': 4461117, 'author': 'bortunac', 'author_id': 544803, 'author_profile': 'https://Stackoverflow.com/users/544803', 'pm_score': 6, 'selected': False, 'text': '<p>(Do not try this on a big table, the result might be... surprising !)</p>\n<blockquote>\n<p>TEMPORARY TABLE</p>\n</blockquote>\n<pre><code>DROP TABLE IF EXISTS temp_tb;\nCREATE TEMPORARY TABLE ENGINE=MEMORY temp_tb SELECT * FROM orig_tb;\nALTER TABLE temp_tb DROP col_a, DROP col_f,DROP col_z;    #// MySQL\nSELECT * FROM temp_tb;\n</code></pre>\n<p>DROP syntax may vary for databases <a href="http://www.oracle-base.com/articles/8i/dropping-columns.php" rel="nofollow noreferrer">@Denis Rozhnev</a></p>\n'}, {'answer_id': 4622233, 'author': 'David Poor', 'author_id': 111796, 'author_profile': 'https://Stackoverflow.com/users/111796', 'pm_score': 2, 'selected': False, 'text': '<p>I agree with the "simple" solution of listing all the columns, but this can be burdensome, and typos can cause lots of wasted time.  I use a function "getTableColumns" to retrieve the names of my columns suitable for pasting into a query.  Then all I need to do is to delete those I don\'t want. </p>\n\n<pre><code>CREATE FUNCTION `getTableColumns`(tablename varchar(100)) \n          RETURNS varchar(5000) CHARSET latin1\nBEGIN\n  DECLARE done INT DEFAULT 0;\n  DECLARE res  VARCHAR(5000) DEFAULT "";\n\n  DECLARE col  VARCHAR(200);\n  DECLARE cur1 CURSOR FOR \n    select COLUMN_NAME from information_schema.columns \n    where TABLE_NAME=@table AND TABLE_SCHEMA="yourdatabase" ORDER BY ORDINAL_POSITION;\n  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;\n  OPEN cur1;\n  REPEAT\n       FETCH cur1 INTO col;\n       IF NOT done THEN \n          set res = CONCAT(res,IF(LENGTH(res)&gt;0,",",""),col);\n       END IF;\n    UNTIL done END REPEAT;\n  CLOSE cur1;\n  RETURN res;\n</code></pre>\n\n<p>Your result returns a comma delimited string, for example...</p>\n\n<blockquote>\n  <p>col1,col2,col3,col4,...col53</p>\n</blockquote>\n'}, {'answer_id': 6624383, 'author': 'cwd', 'author_id': 288032, 'author_profile': 'https://Stackoverflow.com/users/288032', 'pm_score': 3, 'selected': False, 'text': "<p>My main problem is the many columns I get when joining tables. While this is not the answer to your question (how to select all but certain columns from <em>one</em> table), I think it is worth mentioning that you can specify <code>table.<em></code> to get all columns from a particular table, instead of just specifying <code></em></code>. </p>\n\n<p>Here is an example of how this could be very useful:</p>\n\n<pre>\nselect users.*, phone.meta_value as phone, zipcode.meta_value as zipcode\n\nfrom users\n\nleft join user_meta as phone\non ( (users.user_id = phone.user_id) AND (phone.meta_key = 'phone') )\n\nleft join user_meta as zipcode\non ( (users.user_id = zipcode.user_id) AND (zipcode.meta_key = 'zipcode') )\n\n</pre>\n\n<p>The result is all the columns from the users table, and two additional columns which were joined from the meta table.</p>\n"}, {'answer_id': 8035440, 'author': 'neurotico79', 'author_id': 1033534, 'author_profile': 'https://Stackoverflow.com/users/1033534', 'pm_score': 2, 'selected': False, 'text': '<p>The answer posted by Mahomedalid has a small problem:</p>\n\n<p>Inside replace function code was replacing "<code>&lt;columns_to_delete&gt;,</code>" by "", this replacement has a problem if the field to replace is the last one in the concat string due to the last one doesn\'t have the char comma "," and is not removed from the string.</p>\n\n<p>My proposal:</p>\n\n<pre><code>SET @sql = CONCAT(\'SELECT \', (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME),\n                  \'&lt;columns_to_delete&gt;\', \'\\\'FIELD_REMOVED\\\'\')\n           FROM INFORMATION_SCHEMA.COLUMNS\n           WHERE TABLE_NAME = \'&lt;table&gt;\'\n             AND TABLE_SCHEMA = \'&lt;database&gt;\'), \' FROM &lt;table&gt;\');\n</code></pre>\n\n<p>Replacing <code>&lt;table&gt;</code>, <code>&lt;database&gt;</code> and `</p>\n\n<p>The column removed is replaced by the string "FIELD_REMOVED" in my case this works because I was trying to safe memory. (The field I was removing is a BLOB of around 1MB)</p>\n'}, {'answer_id': 10598274, 'author': 'Junaid', 'author_id': 806440, 'author_profile': 'https://Stackoverflow.com/users/806440', 'pm_score': 3, 'selected': False, 'text': "<p>I liked the answer from <code>@Mahomedalid</code> besides this fact informed in comment from <code>@Bill Karwin</code>. The possible problem  raised by <code>@Jan Koritak</code> is true I faced that but I have  found a trick for that and just want to share it here for anyone facing the issue.</p>\n\n<p>we can replace the REPLACE function with where clause in the sub-query of Prepared statement like this:</p>\n\n<p>Using my table and column name</p>\n\n<pre><code>SET @SQL = CONCAT('SELECT ', (SELECT GROUP_CONCAT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'users' AND COLUMN_NAME NOT IN ('id')), ' FROM users');\nPREPARE stmt1 FROM @SQL;\nEXECUTE stmt1;\n</code></pre>\n\n<p>So, this is going to exclude only the field <code>id</code> but not <code>company_id</code></p>\n"}, {'answer_id': 11060861, 'author': 'hahakubile', 'author_id': 709096, 'author_profile': 'https://Stackoverflow.com/users/709096', 'pm_score': 2, 'selected': False, 'text': '<p>Based on @Mahomedalid answer, I have done some improvements to support "select all columns except some in mysql"</p>\n\n<pre><code>SET @database    = \'database_name\';\nSET @tablename   = \'table_name\';\nSET @cols2delete = \'col1,col2,col3\';\n\nSET @sql = CONCAT(\n\'SELECT \', \n(\n    SELECT GROUP_CONCAT( IF(FIND_IN_SET(COLUMN_NAME, @cols2delete), NULL, COLUMN_NAME ) )\n    FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tablename AND TABLE_SCHEMA = @database\n), \n\' FROM \',\n@tablename);\n\nSELECT @sql;\n</code></pre>\n\n<p>If you do have a lots of cols, use this sql to change group_concat_max_len</p>\n\n<pre><code>SET @@group_concat_max_len = 2048;\n</code></pre>\n'}, {'answer_id': 15012522, 'author': 'Nick', 'author_id': 1953467, 'author_profile': 'https://Stackoverflow.com/users/1953467', 'pm_score': -1, 'selected': False, 'text': '<p>Im pretty late at throing out an answer for this, put this is the way i have always done it and frankly, its 100 times better and neater than the best answer, i only hope someone will see it. And find it useful</p>\n\n<pre><code>    //create an array, we will call it here. \n    $here = array();\n    //create an SQL query in order to get all of the column names\n    $SQL = "SHOW COLUMNS FROM Table";\n        //put all of the column names in the array\n        foreach($conn-&gt;query($SQL) as $row) {\n            $here[] = $row[0];\n        }\n    //now search through the array containing the column names for the name of the column, in this case i used the common ID field as an example\n    $key = array_search(\'ID\', $here);\n    //now delete the entry\n    unset($here[$key]);\n</code></pre>\n'}, {'answer_id': 15571552, 'author': 'Kilise', 'author_id': 1715913, 'author_profile': 'https://Stackoverflow.com/users/1715913', 'pm_score': 2, 'selected': False, 'text': '<p>I wanted this too so I created a function instead.</p>\n\n<pre><code>public function getColsExcept($table,$remove){\n    $res =mysql_query("SHOW COLUMNS FROM $table");\n\n    while($arr = mysql_fetch_assoc($res)){\n        $cols[] = $arr[\'Field\'];\n    }\n    if(is_array($remove)){\n        $newCols = array_diff($cols,$remove);\n        return "`".implode("`,`",$newCols)."`";\n    }else{\n        $length = count($cols);\n        for($i=0;$i&lt;$length;$i++){\n            if($cols[$i] == $remove)\n                unset($cols[$i]);\n        }\n        return "`".implode("`,`",$cols)."`";\n    }\n}\n</code></pre>\n\n<p>So how it works is that you enter the table, then a column you don\'t want or as in an array: array("id","name","whatevercolumn")</p>\n\n<p>So in select you could use it like this:</p>\n\n<pre><code>mysql_query("SELECT ".$db-&gt;getColsExcept(\'table\',array(\'id\',\'bigtextcolumn\'))." FROM table");\n</code></pre>\n\n<p>or</p>\n\n<pre><code>mysql_query("SELECT ".$db-&gt;getColsExcept(\'table\',\'bigtextcolumn\')." FROM table");\n</code></pre>\n'}, {'answer_id': 17093312, 'author': 'Sean O', 'author_id': 5701, 'author_profile': 'https://Stackoverflow.com/users/5701', 'pm_score': 5, 'selected': False, 'text': '<p>If you are looking to exclude the value of a field, e.g. for security concerns / sensitive info, you can retrieve that column as null.</p>\n\n<p>e.g.</p>\n\n<pre><code>SELECT *, NULL AS salary FROM users\n</code></pre>\n'}, {'answer_id': 17719025, 'author': 'Suraj', 'author_id': 1065227, 'author_profile': 'https://Stackoverflow.com/users/1065227', 'pm_score': 4, 'selected': False, 'text': '<p>While trying the solutions by @Mahomedalid and @Junaid I found a problem. So thought of sharing it. If the column name is having spaces or hyphens like check-in then the query will fail. The simple workaround is to use backtick around column names. The modified query is below</p>\n\n<pre><code>SET @SQL = CONCAT(\'SELECT \', (SELECT GROUP_CONCAT(CONCAT("`", COLUMN_NAME, "`")) FROM\nINFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = \'users\' AND COLUMN_NAME NOT IN (\'id\')), \' FROM users\');\nPREPARE stmt1 FROM @SQL;\nEXECUTE stmt1;\n</code></pre>\n'}, {'answer_id': 17747307, 'author': 'Bhavik Shah', 'author_id': 1680256, 'author_profile': 'https://Stackoverflow.com/users/1680256', 'pm_score': 2, 'selected': False, 'text': "<p>May be I have a solution to <strong>Jan Koritak's</strong> pointed out discrepancy</p>\n\n<pre><code>SELECT CONCAT('SELECT ',\n( SELECT GROUP_CONCAT(t.col)\nFROM\n(\n    SELECT CASE\n    WHEN COLUMN_NAME = 'eid' THEN NULL\n    ELSE COLUMN_NAME\n    END AS col \n    FROM INFORMATION_SCHEMA.COLUMNS \n    WHERE TABLE_NAME = 'employee' AND TABLE_SCHEMA = 'test'\n) t\nWHERE t.col IS NOT NULL) ,\n' FROM employee' );\n</code></pre>\n\n<p><strong>Table :</strong> </p>\n\n<pre><code>SELECT table_name,column_name \nFROM INFORMATION_SCHEMA.COLUMNS \nWHERE TABLE_NAME = 'employee' AND TABLE_SCHEMA = 'test'\n</code></pre>\n\n<p>================================</p>\n\n<pre><code>table_name  column_name\nemployee    eid\nemployee    name_eid\nemployee    sal\n</code></pre>\n\n<p>================================</p>\n\n<p><strong>Query Result:</strong></p>\n\n<pre><code>'SELECT name_eid,sal FROM employee'\n</code></pre>\n"}, {'answer_id': 20396621, 'author': 'Héctor Paúl Cervera-García', 'author_id': 2814721, 'author_profile': 'https://Stackoverflow.com/users/2814721', 'pm_score': 1, 'selected': False, 'text': '<p>I would like to add another point of view in order to solve this problem, specially if you have a small number of columns to remove.</p>\n\n<p>You could use a DB tool like <a href="http://www.mysql.fr/products/workbench/" rel="nofollow">MySQL Workbench</a> in order to generate the select statement for you, so you just have to manually remove those columns for the generated statement and copy it to your SQL script.</p>\n\n<p>In MySQL Workbench the way to generate it is: </p>\n\n<p>Right click on the table -> send to Sql Editor -> Select All Statement.</p>\n'}, {'answer_id': 21169876, 'author': 'HLGEM', 'author_id': 9034, 'author_profile': 'https://Stackoverflow.com/users/9034', 'pm_score': -1, 'selected': False, 'text': '<p>Select * is a SQL antipattern. It should not be used in production code for many reasons including:</p>\n\n<p>It takes a tiny bit longer to process. When things are run millions of times, those tiny bits can matter. A slow database where the slowness is caused by this type of sloppy coding throughout is the hardest kind to performance tune.</p>\n\n<p>It means you are probably sending more data than you need which causes both server and network bottlenecks. If you have an inner join, the chances of sending more data than you need are 100%. </p>\n\n<p>It causes maintenance problems especially when you have added new columns that you do not want seen everywhere. Further if you have a new column, you may need to do something to the interface to determine what to do with that column. </p>\n\n<p>It can break views (I know this is true in SQl server, it may or may not be true in mysql). </p>\n\n<p>If someone is silly enough to rebuild the tables with the columns in a differnt order (which you shouldn\'t do but it happens all teh time), all sorts of code can break. Espcially code for an insert for example where suddenly you are putting the city into the address_3 field becasue without specifying, the database can only go on the order of the columns.  This is bad enough when the data types change but worse when the swapped columns have the same datatype becasue you can go for sometime inserting bad data that is a mess to clean up.  You need to care about data integrity. </p>\n\n<p>If it is used in an insert, it will break the insert if a new column is added in one table but not the other. </p>\n\n<p>It might break triggers. Trigger problems can be difficult to diagnose. </p>\n\n<p>Add up all this against the time it take to add in the column names (heck you may even have an interface that allows you to drag over the columns names (I know I do in SQL Server, I\'d bet there is some way to do this is some tool you use to write mysql queries.) Let\'s see, "I can cause maintenance problems, I can cause performance problems and I can cause data integrity problems, but hey I saved five minutes of dev time." Really just put in the specific columns you want.</p>\n\n<p>I also suggest you read this book:\n<a href="https://rads.stackoverflow.com/amzn/click/com/B00A376BB2" rel="nofollow noreferrer" rel="nofollow noreferrer">http://www.amazon.com/SQL-Antipatterns-Programming-Pragmatic-Programmers-ebook/dp/B00A376BB2/ref=sr_1_1?s=digital-text&amp;ie=UTF8&amp;qid=1389896688&amp;sr=1-1&amp;keywords=sql+antipatterns</a></p>\n'}, {'answer_id': 31871062, 'author': 'neelabh', 'author_id': 633638, 'author_profile': 'https://Stackoverflow.com/users/633638', 'pm_score': 2, 'selected': False, 'text': "<p>I agree with @Mahomedalid's answer, but I didn't want to do something like a prepared statement and I didn't want to type all the fields, so what I had was a silly solution. </p>\n\n<p>Go to the table in phpmyadmin->sql->select, it dumps the query: copy, replace and done! :)</p>\n"}, {'answer_id': 56431536, 'author': 'Eaten by a Grue', 'author_id': 1767412, 'author_profile': 'https://Stackoverflow.com/users/1767412', 'pm_score': 1, 'selected': False, 'text': "<p>The accepted answer has several shortcomings.</p>\n\n<ul>\n<li>It fails where the table or column names requires backticks</li>\n<li>It fails if the column you want to omit is last in the list</li>\n<li>It requires listing the table name twice (once for the select and another for the query text) which is redundant and unnecessary</li>\n<li>It can potentially return column names in the <em>wrong order</em></li>\n</ul>\n\n<p>All of these issues can be overcome by simply including backticks in the <code>SEPARATOR</code> for your <code>GROUP_CONCAT</code> and using a <code>WHERE</code> condition instead of <code>REPLACE()</code>.  For my purposes (and I imagine many others') I wanted the column names returned in the same order that they appear in the table itself.  To achieve this, here we use an explicit <code>ORDER BY</code> clause inside of the <code>GROUP_CONCAT()</code> function:</p>\n\n<pre><code>SELECT CONCAT(\n    'SELECT `',\n    GROUP_CONCAT(COLUMN_NAME ORDER BY `ORDINAL_POSITION` SEPARATOR '`,`'),\n    '` FROM `',\n    `TABLE_SCHEMA`,\n    '`.`',\n    TABLE_NAME,\n    '`;'\n)\nFROM INFORMATION_SCHEMA.COLUMNS\nWHERE `TABLE_SCHEMA` = 'my_database'\n    AND `TABLE_NAME` = 'my_table'\n    AND `COLUMN_NAME` != 'column_to_omit';\n</code></pre>\n"}, {'answer_id': 57268792, 'author': 'MR_AMDEV', 'author_id': 10114772, 'author_profile': 'https://Stackoverflow.com/users/10114772', 'pm_score': 1, 'selected': False, 'text': '<p>I have a suggestion but not a solution.\nIf some of your columns have a larger data sets then you should try with following</p>\n\n<pre><code>SELECT *, LEFT(col1, 0) AS col1, LEFT(col2, 0) as col2 FROM table\n</code></pre>\n'}, {'answer_id': 64573062, 'author': 'Adam', 'author_id': 2311074, 'author_profile': 'https://Stackoverflow.com/users/2311074', 'pm_score': 0, 'selected': False, 'text': '<p>If you use MySQL Workbench you can right-click your table and click <code>Send to sql editor</code> and then <code>Select All Statement</code> This will create an statement where all fields are listed, like this:</p>\n<pre><code>SELECT `purchase_history`.`id`,\n    `purchase_history`.`user_id`,\n    `purchase_history`.`deleted_at`\nFROM `fs_normal_run_2`.`purchase_history`;\nSELECT * FROM fs_normal_run_2.purchase_history;\n</code></pre>\n<p>Now you can just remove those that you dont want.</p>\n'}, {'answer_id': 65052272, 'author': 'DropHit', 'author_id': 1656124, 'author_profile': 'https://Stackoverflow.com/users/1656124', 'pm_score': 2, 'selected': False, 'text': '<p>I use this work around although it may be &quot;Off topic&quot; - using mysql workbench and the query builder -</p>\n<ol>\n<li>Open the columns view</li>\n<li>Shift select all the columns you want in your query (in your case all but one which is what i do)</li>\n<li>Right click and select send to SQL Editor-&gt; name short.</li>\n<li>Now you have the list and you can then copy paste the query to where ever.</li>\n</ol>\n<p><a href="https://i.stack.imgur.com/NNnAY.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/NNnAY.png" alt="enter image description here" /></a></p>\n'}, {'answer_id': 70622146, 'author': 'Lukas Eder', 'author_id': 521799, 'author_profile': 'https://Stackoverflow.com/users/521799', 'pm_score': 0, 'selected': False, 'text': '<p>The question was about MySQL, but I still think it\'s worth mentioning that at least <a href="https://blog.jooq.org/the-useful-bigquery-except-syntax/" rel="nofollow noreferrer">Google BigQuery and H2 support a <code>* EXCEPT</code> syntax natively</a>, e.g.</p>\n<pre class="lang-sql prettyprint-override"><code>SELECT * FROM actor\n</code></pre>\n<p>Producing:</p>\n<pre><code>|actor_id|first_name|last_name   |last_update            |\n|--------|----------|------------|-----------------------|\n|1       |PENELOPE  |GUINESS     |2006-02-15 04:34:33.000|\n|2       |NICK      |WAHLBERG    |2006-02-15 04:34:33.000|\n|3       |ED        |CHASE       |2006-02-15 04:34:33.000|\n</code></pre>\n<p>Whereas</p>\n<pre class="lang-sql prettyprint-override"><code>SELECT * EXCEPT (last_update) FROM actor\n</code></pre>\n<p>Producing:</p>\n<pre><code>|actor_id|first_name|last_name   |\n|--------|----------|------------|\n|1       |PENELOPE  |GUINESS     |\n|2       |NICK      |WAHLBERG    |\n|3       |ED        |CHASE       |\n</code></pre>\n<p>Maybe, a future version of MySQL will support this syntax as well?</p>\n'}]
['https://Stackoverflow.com/questions/9122', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/44/']