Select To Include All Fields Except XYZ

Let's say a SQL table has 20 fields.  Is there a way to select all, followed by an exclude statement?  Something like...

   SELECT * EXCLUDE effectiveYear, effectiveMonth FROM TableName

It would be easier including 20 fields, and removing 2 fields via EXCLUDE-like statement than explicitly entering 18 field names separated by commas.
LVL 1
LarryAndroAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SharathData EngineerCommented:

Query 1 :

SELECT name + ',' FROM sys.columns WHERE object_id = OBJECT_id('YourTableName')

will display all the column names seperated by comma in your table. You can copy-paste the result set and delete the columnnames whichever you dont want and create your SQL statement like

Query 2:

SELECT <column names returned by above query>
 FROM YourTableName

You can save the query result of my first query in to a local varibale and use it to create second query. But if you dont have more tables then copy-paste result set and delete the column names whichever you dont want is easy process.
You can re-write the first query also like below:

SELECT name + ',' FROM sys.columns WHERE object_id = OBJECT_id('YourTableName') AND name <> 'YourColumnName'

Hope this will help you.

0
folderolCommented:
this may be less than helpful since it is MSSQL syntax, I don't have a PL/SQL server.  However, if you can modify it a bit, it generates simple select from queries.

The parameters are tablename, alias for this table in your eventual query, and datatype to include.  

I'm pretty liberal with this last parameter, for example my databases tend to follow naming conventions so I can often find foreign keys by asking for columns that end in ID.  I just cut and paste from the results pane into the query pane.  However, I most frequently use it with a real datatype, like

view_columns table1, t1, datetime


The third parameter is optional, omitting it returns all columns as this will cause the 'Any' section to execute.  Omitting the 2nd parameter will use the tablename like table1.column1, and using null for the 2nd parameter like this

view_columns table1,null, nvarchar

will display the columns name only.  
CREATE PROCEDURE [dbo].[view_columns] @tablename varchar(40), @tableabbr varchar(40) = @tablename , @coltype varchar(20)  = 'Any'
AS
declare @tblpre as varchar(40)
set @tblpre = isnull(@tableabbr + '.','')
 
if @coltype ='Any'
begin
	select 
	'col' = @tblpre+ltrim(left(syscolumns.name,40))+',', colid, 'type' = type_name(syscolumns.xtype)
	from 
	syscolumns Join sysobjects on syscolumns.id = sysobjects.id 
	where 
	sysobjects.name = @tablename
	order by colid
end
else 
begin
	if upper(@coltype) = 'FK' 
	begin
		select 
		'col' = @tblpre+syscolumns.name+',', colid, 'type' = type_name(syscolumns.xtype)
		from 
		syscolumns Join sysobjects on syscolumns.id = sysobjects.id 
		where 
		sysobjects.name = @tablename and right(syscolumns.name,2) = 'ID'
		order by colid
	end
	else
	begin
		if upper(@coltype) = 'NUMBER' 
		begin
			select 
			'col' = @tblpre+syscolumns.name+',', colid, 'type' = type_name(syscolumns.xtype)
			from 
			syscolumns Join sysobjects on syscolumns.id = sysobjects.id 
			where 
			sysobjects.name = @tablename and right(syscolumns.name,2) <> 'ID' and type_name(syscolumns.xtype) in ('float', 'int', 'decimal', 'numeric')
			order by colid
		end
		else
		begin
			if upper(@coltype) = 'CD' 
			begin
				select 
				'col' = @tblpre+syscolumns.name+',', colid, 'type' = type_name(syscolumns.xtype)
				from 
				syscolumns Join sysobjects on syscolumns.id = sysobjects.id 
				where 
				sysobjects.name = @tablename and right(syscolumns.name,2) <> 'ID' and type_name(syscolumns.xtype) in ('nvarchar', 'varchar', 'nchar', 'char')
				order by colid
			end
			else
			begin
				if upper(@coltype) = 'STRING' 
				begin
					select 
					'col' = @tblpre+syscolumns.name+',', colid, 'type' = type_name(syscolumns.xtype)
					from 
					syscolumns Join sysobjects on syscolumns.id = sysobjects.id 
					where 
					sysobjects.name = @tablename and right(syscolumns.name,2) <> 'ID' and type_name(syscolumns.xtype) in ('nvarchar', 'varchar')
					order by colid
				end
				else
			     	begin
					select 
					'col' = @tblpre+ltrim(left(syscolumns.name,40))+',', colid, 'type' = type_name(syscolumns.xtype)
					from 
					syscolumns Join sysobjects on syscolumns.id = sysobjects.id 
					where 
					type_name(syscolumns.xtype) = @coltype and
					sysobjects.name = @tablename
					order by colid
				end
			end
		end	
	end
end

Open in new window

0
Naveen KumarProduction Manager / Application Support ManagerCommented:
In oracle, there is no direct answer for your question. Either you have to type them or use * to get all fields. If you third party tools like sql navigator or toad or some other, then i think you can do it easily by saying

select * from table1;

then get the complete sql statement for the above which will have all column names and then delete the unwanted column names from it and execute it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LarryAndroAuthor Commented:
Thanks everyone.  I couldn't get the techniques to work from the first two responses, and I'm sure they are because of my version of SQL.  (Intersystems Cache SQL, which is not a mainstream environment.  This forces me to ask my SQL questions generically, then trying them to see if the query parameters are supported.)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.