How to drop fields unwanted in SELECT

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to drop fields unwanted in SELECT

Author

Topic

nissansentra
Starting Member

3 Posts

Posted - 2006-01-30 : 10:07:47

I am joining tables with a lot of fields, but only want to keep 90% of them, say. Instead of inputting 90% of the field names in SELECT, is there a way to drop the 10% unwanted fields? There is a DROP option in SAS Data Step that can handle this situation, but not sure there is one T-SQL. Thanks.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-30 : 10:09:18

DECLARE @s VARCHAR(8000)
SELECT @s=@s + Column_name + ','
from INFORMATION_SCHEMA.Columns
where table_name = 'MyTable' and Column_name != 'Unwanted_Column'
set @s = left(@s, Datalength(@s)-1)
set @s = 'Select ' + @s + ' from MyTable'
execute ( @s)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-31 : 01:41:50

quote:
Originally posted by Srinika

DECLARE @s VARCHAR(8000)
SELECT @s=@s + Column_name + ','
from INFORMATION_SCHEMA.Columns
where table_name = 'MyTable' and Column_name != 'Unwanted_Column'
set @s = left(@s, Datalength(@s)-1)
set @s = 'Select ' + @s + ' from MyTable'
execute ( @s)

Slight change

DECLARE @s VARCHAR(8000)
Select @s=''
SELECT @s=@s + Column_name + ','
from INFORMATION_SCHEMA.Columns
where table_name = 'MyTable' and Column_name != 'Unwanted_Column'
set @s = left(@s, Datalength(@s)-1)
set @s = 'Select ' + @s + ' from MyTable'
execute ( @s)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources