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


Hi,

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

news:1169632863.164067.157770@a75g2000cwd.googlegroups.com...

Hi,

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
sqlw@microsoft.com

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

news:1169634626.302816.276090@v45g2000cwv.googlegroups.com...

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
apostrophes.

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.

wBob

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!

-----------------------------------------------Reply-----------------------------------------------

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

news:1169632863.164067.157770@a75g2000cwd.googlegroups.com...

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
MCDBA
http://www.realsqlguy.com

-----------------------------------------------Reply-----------------------------------------------

Tracy McKibben je napisao/la:

Hi,
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
GO

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]
GO

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

RETURNS VARCHAR(MAX)

AS

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

    Function Name:  dbo.udf_ExcludeColumn

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

    Database:       master

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

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

BEGIN

        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 )

function_exit:

        RETURN @column_list

END
GO

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?

Stu

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