CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
user1630445
user1630445

Reputation: 11

Select Query in T Sql

Need one help in formulating select query.

I have a table with 50 columns in it, now i want to retrieve not all the columns from this table.

i.e, say i have col like: a,b,c,d & i want a select query without column c,d. I know using simply select a, b from table will serve the purpose, but think when there are 50 columns and you want to retrieve only 40 from them.

Is there any specific T-SQL in sql server syntax available that will full fill the requirement.

Upvotes: 1

Views: 192

Answers (5)

Dhruvesh Shah
Dhruvesh Shah

Reputation: 121

If you are using it regularly Creating view will help. although you have to do it once.

Upvotes: 1

SQL
SQL

Reputation: 21

Actually, it is possible to select all the columns except certain ones you specify. It just isn't something you should normally do.

For instance:

DECLARE @columns VARCHAR(MAX)
SELECT @columns = COALESCE(@columns+',' ,'') + name
FROM MyDatabase.sys.columns
WHERE object_id = (
    SELECT object_id
    FROM MyDatabase.sys.tables
    WHERE name='MyTable'
)
    AND name NOT IN ('MyColumn1','MyColumn2')
PRINT @columns

EXEC('SELECT '+@columns+' FROM MyTable')

Generally, it's better to follow the examples given in the other answers.

Upvotes: 2

Scott L Holmes
Scott L Holmes

Reputation: 111

Click New Query - type in "select * from table". Highlight text with cursor. Right click highlight, select Design Query in Editor, clear the check marks for the columns you don't want. Click OK. I find that writing code in the Editor can be really fast once you get used to it. Much faster than waiting for Intellisense try to figure out what you are trying to do.

When you write queries this way, it really helps to have a formatting tool like Poor Man's T-SQL Formatter because the code generated by the Editor is usually unreadable.

Upvotes: 0

Himanshu
Himanshu

Reputation: 32612

You can't do like Select * except c,d from table. You have to write each column name manually. If you don't want to write each column name manually you can use Script Table As by right clicking on table or view in Object Explorer like this:

enter image description here

Then you will get whole SELECT query in New Query Editor Window then remove unwanted column like this:

enter image description here

Upvotes: 1

Aaron Bertrand
Aaron Bertrand

Reputation: 280615

No, you can't say "select all columns except c,d" but if you use Object Explorer, expand the tables or views node, expand your table or view, and drag the "Columns" folder onto the query window. Now you can delete the 10 columns from the list that you don't want, which should be easier than typing out the other 40.

enter image description here

Upvotes: 5

Related Questions