Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Is there a way to select all columns except one or two columns? Expand / Collapse
Author
Message
Posted Tuesday, March 3, 2009 11:39 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, September 26, 2014 1:57 AM
Points: 432, Visits: 622
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
Post #667861
Posted Wednesday, March 4, 2009 12:40 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 39,977, Visits: 36,340
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 2008, MVP
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

Post #667884
Posted Wednesday, March 4, 2009 12:50 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, March 27, 2014 11:17 PM
Points: 309, Visits: 814
You already have the query you are looking for... Then what else you need??

Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
Post #667895
Posted Wednesday, March 4, 2009 2:08 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 26, 2011 7:31 AM
Points: 75, Visits: 262
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')




I Like CTE
My Blog - Arz Kiya Hai!!
Post #667940
Posted Wednesday, March 4, 2009 2:33 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
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
Post #667952
Posted Wednesday, March 4, 2009 2:42 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, September 26, 2014 1:57 AM
Points: 432, Visits: 622
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)

Post #667958
Posted Wednesday, March 4, 2009 2:53 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
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
Post #667964
Posted Wednesday, March 4, 2009 2:55 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, September 26, 2014 1:57 AM
Points: 432, Visits: 622
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..
Post #667967
Posted Wednesday, March 4, 2009 3:11 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, September 26, 2014 1:57 AM
Points: 432, Visits: 622
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
Post #667975
Posted Wednesday, March 4, 2009 3:34 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 26, 2011 7:31 AM
Points: 75, Visits: 262
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')



I Like CTE
My Blog - Arz Kiya Hai!!
Post #667980
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse