SQL SELECT * FROM tbl (HIDE 1 COLUMN)

Question

I'm trying to do SELECT * FROM tbl, but out of 20 columns I don't want 1 particular column to be selected. Is there a dynamic way to do so? Just hate to specify 19 columns in the query!

Only 19? Pshaw. Seriously though, make it a stored procedure if it is that much of an issue for you. Or this: http://stackoverflow.com/questions/9122/select-all-columns-except-one-in-mysql/1495476#1495476 — josh.trow, Jun 21 '11 at 14:27
See http://stackoverflow.com/questions/729197/sql-exclude-a-column-using-select-except-columna-from-tablea — borrible, Jun 21 '11 at 14:28
@josh.trow : dynamic SQL isn't really an answer: the contract and resultset can change randomly as the table changes. — gbn, Jun 21 '11 at 15:03

score 9 · Accepted Answer · answered Jun 21 '11 at 14:28

Just specify the columns. You should be doing that all the time anyway as select * is a very poor programming choice. I don't know about mysql but in SQL Server I can drag all the columns over from the object browser and delete the ones I don't want, it takes seconds. Perhaps the interface you use has a similar functionality.

score 2 · Answer 2 · edited May 23 '17 at 10:27

2

Try this out. Like he says, the SQL isn't pretty.

Select all columns except one in MySQL?

edited May 23 '17 at 10:27

Community

1
1

answered Jun 21 '11 at 14:29

Josh

8,082
5
43
41

score 1 · Answer 3 · answered Jun 21 '11 at 14:28

Well you (or I) could spend a bit of time writing some dynamic sql to query the columns in a table, generating a sql statement minus the column you want to ignore, then executing that dynamic sql.

Or you could take a deep breath and code it up for the 19 columns.

I know which I'd choose. I know which way is more maintainable and less error prone.

score 0 · Answer 4 · edited Jun 18 '20 at 20:58

DELIMITER ;;
CREATE PROCEDURE sp_hide_cols_sql_all(v_table varchar(60))
BEGIN

 DECLARE v_query VARCHAR(1000);

    SELECT CONCAT('SELECT ', GROUP_CONCAT(distinct c.column_name) , ' FROM  ',v_table) INTO v_query from information_schema.`COLUMNS` c where c.table_name = v_table and c.column_name<>'id' and table_schema=DATABASE();

           SET @sql = v_query;
           PREPARE stmt FROM @sql;
           EXECUTE stmt;
           DEALLOCATE PREPARE stmt;
END ;;
DELIMITER ;

CALL sp_hide_cols_sql_all('table_name');

score 0 · Answer 5 · answered Jun 21 '11 at 14:29

Interesting question but the direct answer is no, that is not possible. However, you can use a view or a stored procedure that will allow you to write out the 19 columns once and then just call the stored procedure or view using a SELECT * FROM view or exec storedproc

The only issue here is that if you add/remove/renamed a column to your table, you will need to update your object (view or stored procedure) to reflect the new/removed/renamed column.

score 0 · Answer 6 · answered Jun 21 '11 at 14:30

0

insded of star write the required column name In that ,, e.i is

SELECT name,password...etc FROM tbl

answered Jun 21 '11 at 14:30

K6t

1,821
1
13
21

score 0 · Answer 7 · answered Jun 21 '11 at 15:57

I would agree with all the answers re. the merits of doing "select * from ..."

However, if you really wanted to, you could do a "select *" into a temporary table, do an "alter table" to drop the column(s) you don't want, and then do a "select *" from the temporary table.

Still... not very nice because the main point is that, should your table structure change (and get some extra columns), consumers of your selected data would now be getting more than they expect. "Select *" is, in most cases, just lazy coding.

SQL SELECT * FROM tbl (HIDE 1 COLUMN)

7 Answers7