SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Is there a way to select all columns except one or two columns?


Is there a way to select all columns except one or two columns?

Author
Message
Santhoshkumar KB
Santhoshkumar KB
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1648 Visits: 1066
Hi,
There is a table with 50 columns:

I need to select 48 or 49 columns only.
Is there any way to do this?

Ex:

select col1,col2,........,col48 from myTable
i do not need col49 and col50
(the two columns might be any two from those 50)



Thanks

Thanks,
Santhosh

Human Knowledge Belongs To The World !!
GilaMonster
GilaMonster
SSC Guru
SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)

Group: General Forum Members
Points: 219329 Visits: 46279
Yup, list all the column names (as you've done in your example)

If you don't want to type, and I don't blame you, then there's a quick way. Expand out object explorer until you can see the table. Expand that out as well. Click on "Columns" and drag that to the query window and you'll have a full list of all the columns in the table, comma delimited. Then you can remove the two that you don't want.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Sakthivel Chidambaram
Sakthivel Chidambaram
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1365 Visits: 838
You already have the query you are looking for... Then what else you need??

Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
JohnSmith001
JohnSmith001
SSC Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 Visits: 281
Hi,
Personly I din't like my own idea, but with dynamic sql you can have a shot-


DECLARE @COLUMNS VARCHAR(1000)
DECLARE @Col1 varchar(100), @Col2 varchar(1000)
SET @COL1 = 'DATE'
SET @COL2 = 'dateId'

SELECT @Columns = SubString (( SELECT ', ' + QUOTENAME(Column_name )
from INFORMATION_SCHEMA.columns
WHERE Table_name ='TimeDimension'
AND COLUMN_NAME != @col1
AND COLUMN_NAME != @COL2

FOR XML PATH ( '' ) ), 3, 1000)

EXEC('SELECT '+ @Columns +' FROM TimeDimension')




John Smith
Florian Reischl
Florian Reischl
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8689 Visits: 3934
Yet another way:


DECLARE @excluded_columns TABLE (name SYSNAME)
DECLARE @table_name SYSNAME
DECLARE @columns NVARCHAR(MAX)

SET @table_name = 'Person.Contact'

-- Excluded columns
INSERT INTO @excluded_columns VALUES ('Suffix')
INSERT INTO @excluded_columns VALUES ('rowguid')

SET @columns = ''
SELECT @columns = @columns + ', ' + QUOTENAME(name)
FROM sys.columns
WHERE object_id = OBJECT_ID(@table_name)

SET @columns = RIGHT(@columns, LEN(@columns) - 2)

EXECUTE ('SELECT ' + @columns + ' FROM ' + @table_name)



Best regards
Flo


The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Santhoshkumar KB
Santhoshkumar KB
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1648 Visits: 1066
florian.reischl (3/4/2009)
Yet another way:


DECLARE @excluded_columns TABLE (name SYSNAME)
DECLARE @table_name SYSNAME
DECLARE @columns NVARCHAR(MAX)

SET @table_name = 'Person.Contact'

-- Excluded columns
INSERT INTO @excluded_columns VALUES ('Suffix')
INSERT INTO @excluded_columns VALUES ('rowguid')

SET @columns = ''
SELECT @columns = @columns + ', ' + QUOTENAME(name)
FROM sys.columns
WHERE object_id = OBJECT_ID(@table_name)

SET @columns = RIGHT(@columns, LEN(@columns) - 2)

EXECUTE ('SELECT ' + @columns + ' FROM ' + @table_name)



Best regards
Flo

Hi florian,
Thanks
I think one line is missed out here
I have added it below:

DECLARE @excluded_columns TABLE (name SYSNAME)
DECLARE @table_name SYSNAME
DECLARE @columns NVARCHAR(MAX)

SET @table_name = 'csd_temp_1'

-- Excluded columns
INSERT INTO @excluded_columns VALUES ('Recommendations')
INSERT INTO @excluded_columns VALUES ('Lessons Learned')

SET @columns = ''
SELECT @columns = @columns + ', ' + QUOTENAME(name)
FROM sys.columns
WHERE object_id = OBJECT_ID(@table_name)
--missed line
and name NOT IN(select name from @excluded_columns)

SET @columns = RIGHT(@columns, LEN(@columns) - 2)

EXECUTE ('SELECT ' + @columns + ' FROM ' + @table_name)



Thanks,
Santhosh

Human Knowledge Belongs To The World !!
Florian Reischl
Florian Reischl
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8689 Visits: 3934
Oups... did not yet get my breakfast...

Thanks and sorry
Flo


The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Santhoshkumar KB
Santhoshkumar KB
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1648 Visits: 1066
florian.reischl (3/4/2009)
Oups... did not yet get my breakfast...

Thanks and sorry
Flo

Its ok Flo,
that gave me a chance to think a little.. Cool

Thanks,
Santhosh

Human Knowledge Belongs To The World !!
Santhoshkumar KB
Santhoshkumar KB
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1648 Visits: 1066
Thanks Flo and Gail,

but the requirement is
1. to compare two tables
2. in these two tables there are 50 columns
3. 1 or 2 or 3(max) columns are of type NTEXT
4. so excluding those 1 or 2 or 3 columns i need to compare all remaining columns.

the code i'm using for this is:
CODE_BLOCK-1

select 'table1' as tblName, * from
(select * from table1
except
select * from table2) x
union all
select 'table2' as tblName, * from
(select * from table2
except
select *
from table1) x


If I need to write all the columns it will be confusing(rather than confusing it will be difficult)

now I have this code:
CODE_BLOCK-2

DECLARE @excluded_columns TABLE (name SYSNAME)
DECLARE @table_name SYSNAME
DECLARE @columns NVARCHAR(MAX)

SET @table_name = 'csd_temp_1'

-- Excluded columns
INSERT INTO @excluded_columns VALUES ('Recommendations')
INSERT INTO @excluded_columns VALUES ('Lessons Learned')

SET @columns = ''
SELECT @columns = @columns + ', ' + QUOTENAME(name)
FROM sys.columns
WHERE object_id = OBJECT_ID(@table_name)
--missed line
and name NOT IN(select name from @excluded_columns)

SET @columns = RIGHT(@columns, LEN(@columns) - 2)

EXECUTE ('SELECT ' + @columns + ' FROM ' + @table_name)



i'm planning to create a procedure combinig the above 2 code blocks
input parameters will be: table1, table2, col1, col2, .....coln (NTEXT columns)

I need help in this, I do not know how many NTEXT columns will be there,
so the procedure should accept all the input parameters(first 2 being table names and remaining NTEXT columns)

In CODE_BLOCK-2, I need the sproc to insert the input NTEXT columns into @excluded_columns as below

-- Excluded columns
INSERT INTO @excluded_columns VALUES ('col1')
INSERT INTO @excluded_columns VALUES ('col2')
INSERT INTO @excluded_columns VALUES ('col3')
.
INSERT INTO @excluded_columns VALUES ('coln')


I am just trying to make it in general rather than only for a fixed columns.

I will update here whatever I have done ASAP.
Please let me know if this post is confusing, I will try to modify...

Thanks
San

Thanks,
Santhosh

Human Knowledge Belongs To The World !!
JohnSmith001
JohnSmith001
SSC Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 Visits: 281
Santhosh (3/4/2009)
Thanks Flo and Gail,

but the requirement is
1. to compare two tables
2. in these two tables there are 50 columns
3. 1 or 2 or 3(max) columns are of type NTEXT
4. so excluding those 1 or 2 or 3 columns i need to compare all remaining columns.

Thanks
San


Then it is very easy without column variable


DECLARE @COLUMNS VARCHAR(1000)
DECLARE @Col1 varchar(100), @Col2 varchar(1000)


SELECT @Columns = SubString (( SELECT ', ' + QUOTENAME(Column_name )
from INFORMATION_SCHEMA.columns
WHERE Table_name ='TimeDimension'
AND Data_type != 'Ntext'

FOR XML PATH ( '' ) ), 3, 1000)

EXEC('SELECT '+ @Columns +' FROM TimeDimension')



John Smith
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search