SQL Server 2008 column select

In SQL Server, if I got a table with like 20 columns and I want 18 of them, can I say something like * minus columnname1, c开发者_如何学编程olumnname2, course right now I write them all.

But if you could it would be much easier.

---

Little hint to replace the asterisk with column names in SQL Management Studio in no time without any fancy plugin:

1. Select your written query (no matter how many joins, etc.)
2. Right click and select "Design Query in Editor..."
3. Simply click "Ok"

The asterisk should have been expanded to column names now :)

Ofc it's possible to select/deselect any column in the query editor..

Hth

---

It is not possible. However if you are using SQL Server Management Studio 2008 / 2005 you can right click on the table and select the "Script Table as > SELECT To" menu option. This will save you typing the column names, or purchase Red-Gate's SQL Prompt

---

Out of the box - no, it's not possible. You have to spell out all the columns you want explicitly.

With SQL Server Management Studio 2008, there is intellisense which can help you select columns from a table - so that's certainly one step to help ease the pain.

Add-in tools like SQL Prompt offer more help - in SQL Prompt, you can type

SELECT *
FROM dbo.YourTable

and if you have the cursor just after the asterisk symbol (*), you can press <TAB> and expand the asterisk into the list of all columns for that table (and then remove the two you don't want) - or you can popup a window and pick those columns you really want.

Very handy, very useful, very much speeding up development - but it's not a free tool.....

---

You can use select TOP (18) * from givenTable is you want 18 rows.

There is no such method for columns. In fact column names are stored in master db and you can extract them and consruct query looking like what you are asking for BUT it would not be easier than just select field1,field2 ... field18 from blaBlaBla.

SELECT table_name=sysobjects.name,
         column_name=syscolumns.name,
         datatype=systypes.name,
         length=syscolumns.length
    FROM sysobjects 
    JOIN syscolumns ON sysobjects.id = syscolumns.id
    JOIN systypes ON syscolumns.xtype=systypes.xtype
   WHERE sysobjects.xtype='U'
         and sysobjects.name='myTableName'
ORDER BY sysobjects.name,syscolumns.colid

will give you the list of your columns. You can write select generator based on this query.

---

I'd like to add to the answer of, "No, it's not possible directly in SQL". I would love to have that feature too! It sucks when you're trying to do some quick debugging on a 10+ column table that has a varbinary(max).

But I really just want to point out an alternative to Kane's tip for SSMS 2008 (Sql Server Management Studio).

If you open the Object Explorer (right-click in the query window and choose "Open Server in Object Explorer"), navigate to the node for the table in question. Expand the node so you can see the "Columns" node. Now "drag" the Columns node over to your query window and "drop" it. It will paste in all the column names for the table--and you can use it directly in a SELECT clause.

---

It is not possible as far as I know.

---

I have created a script for easy copy/pasting multiple columns, you might find it useful. See:

http://www.sqlservercentral.com/scripts/102375/

The script is explained in detail there, but in short for those who do not have an account on sqlservercentral: It's a stored procedure that i can run using a shortcut. Type in your tablename (also works with temp tables and views), highlight it, hit the shortcut and it will display the columns of the table. From there you can easily copy multiple columns (the columns are also shown with a comma in front of the column name, so that also saves you some typing) and paste it in your query screen.

 CREATE PROCEDURE [dbo].[sp_ColumnSelect]
    @FullObjectName varchar(200)
AS
/*
    Author: Robin van Schaik
    Version: 1.3 (03-OCT-2012)
*/

DECLARE @Object varchar(200)
DECLARE @Schema varchar(200)
DECLARE @Database varchar(200)
DECLARE @IsTempTable bit
-- Break down parameter in Database/Schema/Object
SET @Object = PARSENAME(@FullObjectName,1)
SET @Schema = ISNULL(PARSENAME(@FullObjectName,2),'dbo')
SET @IsTempTable = case when left(@Object,1)='#' then 1 else 0 end
SET @Database = case when @IsTempTable=1 then 'tempdb' else PARSENAME(@FullObjectName,3) end

EXEC(
'SELECT 
    b.Name as ColumnStart
,   '',''+b.Name as ColumnNext
,   ''[''+b.Name+'']'' as ColumnStartBr
,   '',[''+b.Name+'']'' as ColumnNextBr
FROM
' +@Database+'.sys.objects a
INNER JOIN
' +@Database+'.sys.columns b
ON a.object_id=b.object_id
INNER JOIN
' +@Database+'.sys.schemas d
ON a.schema_id=d.schema_id
WHERE
    a.Object_ID=OBJECT_ID('''+@Database+'.'+@Schema+'.'+@Object+''')
AND d.name = '''+@Schema+'''
'
)