Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language

Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

Select all but one column


i have problem / suggestion.

I need to select all columns but one (id) because i need to make insert
into other table where it will get new ID
I know i can write

select col2,col3,..... colx from ....

i am interesting if exist something like
select *[EXCLUDE id]  from tablename


No, why, you have 1000 columns ? :-))))

"jjxjjx" <jjxjjx2@yahoo.com> wrote in message



i have cca 100 columns, but during development i would like to use
feature  :)))    described in my post
u can call me lazy , but one more feature wont hurt :)

Uri Dimant je napisao/la:

Send it to

"jjxjjx" <jjxjjx2@yahoo.com> wrote in message


If you're using SQL 2005, Management Studio now allows you to use Regular
Expressions to Find and Replace in your text.

Don't be scared, follow my instructions for an easy way to generate a
comma-separated list:

Press Ctrl + D - return results as grid

In your SQL, highlight the name of table you want, remember to use Ctrl + W
to select the whole word

Press Alt + F1 - executes sp_help for the table, returning a list of columns
and other data.
Press the 'Column_name' column in the results from sp_help and copy (Ctrl+C)
or use your mouse.

Now paste the results into your SQL window

Highlight the new column of text you've just pasted in from top to bottom
and press Ctrl+H to Find and replace.

In the 'Find what:' box type '\n', (without the apostrophes).  This is
regular expression shorthand for a carriage return.

In the 'Replace with:' box type ', ', (comma and space) without the

Make sure the 'Look in:' box says 'Selection'

Make sure you check the 'Use' checkbox and pick 'Regular Expressions'

So you're now saying "Replace all carriage returns with a comma and a space".

Now press 'Replace All'.

Hopefully your column of column names is now a comma-separated list.

You're done!

Press Ctrl + T to return results as text if you prefer, but you can't select
the column on it's own then

Let me know how you get on.


PS I realise all the steps might seem a bit daunting at first but once
you've done it a few times it's easy!


In Query Analyzer and SQL Server Management Studio object browser/explorer,
you can right-click on the table name in the tree, script as SELECT and then
remove the unwanted columns.  A lot less typing is needed with this method.

Hope this helps.

Dan Guzman
SQL Server MVP

"jjxjjx" <jjxjjx2@yahoo.com> wrote in message


Not exactly the answer you're looking for, but a tip - in Query
Analyzer's "Object Browser", if you expand a table, you can drag the
entire "Columns" branch (or individual columns) into the query window to
save yourself some typing.

Tracy McKibben


Tracy McKibben je napisao/la:

tnx all for answer
but i am not looking for GUI solution
i want this aproach in stored procedure.
i dont want to wast your time, it was just an idea....

USE master

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[udf_ExcludeColumn]') AND type in (N'FN', N'IF', N'TF',
N'FS', N'FT'))
        DROP FUNCTION [dbo].[udf_ExcludeColumn]

CREATE FUNCTION dbo.udf_ExcludeColumn
        @table_name             SYSNAME,        
    @column_name    SYSNAME



/************************************************************************** *********************

    Function Name:  dbo.udf_ExcludeColumn

    Description:    Return a comma separated list of the columns in given
table, excluding
                                        given column.

    Database:       master

    SELECT master.dbo.udf_ExcludeColumn( 'spt_fallback_db', 'version' )

*************************************************************************** ********************/


        DECLARE @column_list    VARCHAR(MAX)
        DECLARE @sql                    VARCHAR(MAX)

        -- Get the list of columns
        SELECT @column_list = ISNULL( @column_list, '' )  + column_name + ', '  
        FROM INFORMATION_SCHEMA.columns    
        WHERE table_name = @table_name
          AND column_name <> @column_name
        ORDER BY ordinal_position

        -- Trim trailing comma
        SET @column_list = LEFT( @column_list, LEN(@column_list) - 1 )


        RETURN @column_list


On Jan 30, 6:25 pm, "jjxjjx" <jjxjjx2@yahoo.com> wrote:

Do you mean that you want to generate this list of columns dynamically
at run-time?  Bob's function (below) is pretty neat, but I'd be
concerned about implementing a solution like that in a production
environment; what happens if the table schema changes?


Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc