[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Select To Include All Fields Except XYZ

Posted on 2008-11-17
4
Medium Priority
?
950 Views
Last Modified: 2013-12-07
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.
0
Comment
Question by:LarryAndro
4 Comments
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 400 total points
ID: 22980871

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
 
LVL 19

Assisted Solution

by:folderol
folderol earned 400 total points
ID: 22980911
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
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 1200 total points
ID: 22982380
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
 
LVL 1

Author Closing Comment

by:LarryAndro
ID: 31517580
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

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question