We all know that to select all columns from a table, we can use
SELECT * FROM tableA
Is there a way to exclude column(s) from a table without specifying all the columns?
SELECT * [except columnA] FROM tableA
The only way that I know is to manually specify all the columns and exclude the unwanted column. This is really time consuming so I'm looking for ways to save time and effort on this, as well as future maintenance should the table has more/less columns.
thanks!
no there is no way to do this. maybe you can create custom views if that's feasible in your situation
EDIT May be if your DB supports execution of dynamic sql u could write an SP and pass the columns u don't want to see to it and let it create the query dynamically and return the result to you. I think this is doable in SQL Server atleast
I do not know of any database that supports this (SQL Server, MySQL, Oracle, PostgreSQL). It is definitely not part of the SQL standards so I think you have to specify only the columns you want.
You could of course build your SQL statement dynamically and have the server execute it. But this opens up the possibility for SQL injection..
No.
Maintenance-light best practice is to specific only the required columns.
At least 2 reasons:
Like the others have said there is no way to do this, but if you're using Sql Server a trick that I use is to change the output to comma separated, then do
select top 1 * from table
and cut the whole list of columns from the output window. Then you can choose which columns you want without having to type them all in.
No, there isn't any way to do that, and there is no good reason to do it.
When selecting data you should never use *, you should always specify the fields that you want. The reason is that you want the query to work the same even if you later add another field to the table. Also you specify the order of the fields in the result so that rearranging fields in the table doesn't change the result.
The same would of course apply to * except if it was possible to do.
Well, it is a common best practice to specify which columns you want, instead of just specifying *. So you should just state which fields you want your select to return.
You could create a view that has the columns you wish to select, then you can just select * from the view...
Basically, you cannot do what you would like - but you can get the right tools to help you out making things a bit easier.
If you look at Red-Gate's SQL Prompt, you can type "SELECT * FROM MyTable", and then move the cursor back after the "*", and hit <TAB> to expand the list of fields, and remove those few fields you don't need.
It's not a perfect solution - but a darn good one! :-) Too bad MS SQL Server Management Studio's Intellisense still isn't intelligent enough to offer this feature.......
Marc
Depending on the size of your table, you can export it into Excel and transpose it to have a new table in which the columns of original table will be the rows in new table. Then take it back into your SQL database and select the rows according to the condition and insert them into another new table. Finally export this newer table to Excel and do another transpose to have your desired table and take it back to your SQL database.
Not sure if tranpose can be done within SQL database, if yes then it will be even easier.
Jeff
I think the real reason it's not necessary is to filter out an element in your second language (if you're using one). If you're mixing sql and something else, then you can use select * and then filter out the one result that you don't want from within the other language. For example, create a php array out of the results, and then unset the appropriate element of the array, e.g. unset($array['password']);
I agree with everyone... but if I was going to do something like this I might do it this way:
/* Get the data into a temp table */
SELECT * INTO #TempTable
FROM YourTable
/* Drop the cloumns that are not needed */
ALTER TABLE #TempTable
DROP COLUMN ColumnToDrop
/* Get results and drop temp table */
SELECT * FROM #TempTable
DROP TABLE #TempTable
Yes it's possible (but not recommended).
CREATE TABLE contact (contactid int, name varchar(100), dob datetime)
INSERT INTO contact SELECT 1, 'Joe', '1974-01-01'
DECLARE @columns varchar(8000)
SELECT @columns = ISNULL(@columns + ', ','') + QUOTENAME(column_name)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'contact' AND COLUMN_NAME <> 'dob'
ORDER BY ORDINAL_POSITION
EXEC ('SELECT ' + @columns + ' FROM contact')
In summary you cannot do it, but I disagree with all of the comment above, there "are" scenarios where you can legitimately use a * When you create a nested query in order to select a specific range out of a whole list (such as paging) why in the world would want to specify each column on the outer select statement when you have done it in the inner?
So I can't make this a neasted query?
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.Columns where TABLE_NAME = 'SampleTable' AND COLUMN_NAME != 'SampleColumn'
In SQL Management Studio you can expand the columns in Object Explorer, then drag the Columns tree item into a query window to get a comma separated list of columns.