Solved

select * from tableName except Field names starting with _

Posted on 2009-05-05
41
1,504 Views
Last Modified: 2012-06-27
I need a query to get the rows from a table with all the fields except the one starts with a _

for ex:
tableA contains Name,Address,Age, _No
I need a query that will select only Name,Address,Age.
I can not use SP/Views. must be a single SQL statement.
0
Comment
Question by:udayakumarlm
  • 13
  • 10
  • 6
  • +5
41 Comments
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24311505
You can not dynamicly pick the columns and it not be dynamic sql inside a Stored Procedure.
If this simple query does not do it I do not know what you are wanting here.
If you want dynamic sql in a SP, I can do that if you ask.

Select Name, Address, Age

From TableA

Open in new window

0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 24311528
which filed you wand to check for a _ ? if its name try below


Select Name, Address, Age
From TableA
where Name not like 'a_%'
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24311541
I guess there is a way, you can create dynamic select in stored procedure, all you need to do is, pass tablename as parameter and select dynamic column like:

select column_name from  information_schema.columns where TABLE_NAME='TableA'

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24311575
You can even eliminate column with _ with this filter

select column_name from  information_schema.columns where TABLE_NAME='TableA' and charindex('_',COLUMN_NAME)<=0
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24311601
Ritesh, the question states "I can not use SP/Views."  So our Mission Impossible is to do what you are saying without the SP. :-)
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24311612
:)
0
 
LVL 31

Accepted Solution

by:
RiteshShah earned 500 total points
ID: 24311619
there is only way to do so with below code. may be author can use it with SP, otherwise, forget about this task, can't be done at all.



DECLARE @Cols NVARCHAR(500)

declare @TableName NVARCHAR(50)

SET @TableName='TableA'

SET @Cols=''
 

select COLUMN_NAME from  information_schema.columns where TABLE_NAME=@TableName and charindex('_',COLUMN_NAME)<=0
 

SELECT @Cols=@Cols+ s.column_name + ', ' FROM 

(select COLUMN_NAME from  information_schema.columns where TABLE_NAME='TableA' and charindex('_',COLUMN_NAME)<=0) AS s
 

SET @Cols=LEFT(@Cols,LEN(@Cols)-1)
 

set @cols='SELECT ' + @Cols + ' From ' + @TableName
 

EXECUTE sp_executeSQL @Cols

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24311628
The only answer I know is :

It cannot be done.


Have to use either multi-steps, dynamic sql, use of variables, schema information or create a CLR procedure. If you do need the result, then you will have to use the tools available.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24311638
If running from an application, then what you can do is retrieve the list of column names first from information_schema.columns and then use that list to form your actual query.

So, very much depends on how / where you are needing to call the query from.
0
 
LVL 12

Author Comment

by:udayakumarlm
ID: 24311654
we are using this query as part of an information delivery server and we are expecting lots of requests at any given point of time and any extra query and processing of data will affect the performance. that is why i was looking for a single query.

deployment of the server happens in various locations through a simple setup application and maintaining SP or View is a pain in these cases.

I can not query DB more than once and i can not use SP / Views.
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24311668
You just can't do what you are asking the way you have described it.  If you describe your problem better, one of us can probably tell you how to accomplish it.  But One Query Only will not do it.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24311669
As long as single statement query concern, it can't be possible but yes, you can use SP which seems painful to you.
0
 
LVL 12

Author Comment

by:udayakumarlm
ID: 24311714
I have an application which will generate data dynamically based on the request. the request can contain any fields and any tables across databases. data generated is in a temp table with a field that is used for some calculation purpose.
while generating the data and sending to the calling application i do not want to include this calculation field which is identified by using _ as beginning.  
The application already uses a Query as select * from tableA. i just want to change the way the query generated and don't want to change the structure of the application design.
This application is written in C#.
I did figured out my self that it is not possible without using SP/View. but was trying to see if any of you can solve it the way i want.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24311735
still I think, SP could be the solution, no single statement could do this. Have a look at SP I just created, you can use it if you would like to change the structure or let this thread open for other expert's view.
create proc DynamicSelect

@TableName varchar(20),

@FilterChar char(1)

AS

BEGIN

SET NOCOUNT ON
 

	DECLARE @Cols NVARCHAR(500)

	SET @Cols=''
 

	--gathering column list in @Cols variable by querying Information_Scehma.columns table

	SELECT @Cols=@Cols+ s.column_name + ', ' FROM 

	(select COLUMN_NAME from  information_schema.columns where TABLE_NAME='TableA' and charindex(@FilterChar,COLUMN_NAME)<=0) AS s
 

	SET @Cols=LEFT(@Cols,LEN(@Cols)-1)
 

	set @cols='SELECT ' + @Cols + ' From ' + @TableName
 

	EXECUTE sp_executeSQL @Cols

END
 

--check SP whether it actually works!!!!

Exec DynamicSelect 'DemoTable','_'

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24311747
If you are prepared to change the application so that the query is different from "select * from tablea", then it might e worthwhile considering changing it into something that will give you the desired results.

0
 
LVL 12

Author Comment

by:udayakumarlm
ID: 24311801
sorry guys,
with the kind of load we are expecting and with the kind of deployment restrictions we have I can not use SP.
due to the load changing select statement(building one with all the field values after extracting from the database) is also not feasible for me.
0
 
LVL 8

Expert Comment

by:vinurajr
ID: 24312277
information_schema.columns  is a system view that u dont need to maintain..
then wht...?
or u can make use of system table such as syscolumns then where are u stuck now...? what are u talking about high load and things...? can u explain the cenarios
select name from sys.columns where object_id=object_id('Test') and CHARINDEX('_',name)!=1

select name from syscolumns where id=object_id('Test') and CHARINDEX('_',name)!=1

Open in new window

0
 
LVL 12

Author Comment

by:udayakumarlm
ID: 24312530
I want to hit the server only once per execution. in your scnario i have to send first request to get the list of columns and second request for data. i want only one request that will fetch the data for the applicable fields.
0
 
LVL 8

Expert Comment

by:vinurajr
ID: 24312685
there is only one DB hit I have given u two queries u can choose one.... Then tell ur real scenario Dont assume and Presume....
0
 
LVL 12

Author Comment

by:udayakumarlm
ID: 24312760
thank you for your patience please read the earlier comments. for your info
I have one select the column names which does not start with _
one statement which will use the column names to fetch the data
i do not want to send two requests to the server, i have my limitations.
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 8

Expert Comment

by:vinurajr
ID: 24312846
are u doing this using any application...? what are ur limitations...? is it possible u to guess the Db Load ur talking about...?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24312887
Well, back to my original comment. It cannot be done given the constraints and restrictions you have.

A stored procedure can be / is an integral part of the database, and can be deployed with the database, and then used as per other database objects.

Do not understand "load changing select statement" comment...

But anyway, game over. Can't be done.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 24316328

>>  want to hit the server only once per execution. <<
If you are able to handle the column names in your application from where you are hitting the database, then fetch all the data from your table with
SELECT * FROM YourTable
 Then check each column by column in your application and if it doesnot contin underscore (_), then display that column data in the final output or to the end customer whatever.
0
 
LVL 12

Author Comment

by:udayakumarlm
ID: 24316879
this will affect the performance of the application due to the validations need to be done to check for _
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24316910
Yes, it will, it will also affect the size of the rowset coming back. Honestly, this is one reason where SP's exactly meet the need.

0
 
LVL 40

Expert Comment

by:Sharath
ID: 24317451

udayakumarlm - obviously, it affects your performance. but to implement your requirement (hitting the database only once), there is no other way.
instead of this, for better performance, you have to handle this at database side (with a script or SP as suggested by Ritesh) only.
0
 
LVL 3

Expert Comment

by:GregTSmith
ID: 24358390
Use could probably use caching to avoid multiple query performance hit...

Query the tables and columns available in the database and cache them.  Then use the cache to build your query from the code to execute against the SQL Server.  You could even go as far as building / caching the resulting query for each table rather than the individual fields.  

To get the list of tables / columns:
SELECT 

  '[' + REPLACE(S.name, ']', ']]') + '].[' + REPLACE(T.name, ']', ']]') + ']' [TableName], 

  '[' + REPLACE(C.name, ']', ']]') + ']' [ColumnName] 

FROM 

  sys.schemas S 

  INNER JOIN sys.tables T ON T.schema_id = S.schema_id 

  INNER JOIN sys.columns C ON 

    C.object_id = T.object_id 

      AND LEFT(C.name, 1) != '_' -- filter unwanted column names

ORDER BY 

  T.name, 

  C.column_id 

Open in new window

0
 
LVL 12

Author Comment

by:udayakumarlm
ID: 24361088
the tables are dynamic in nature and some times temp tables (# or ##) also. these tables get created and destroyed almost every five to ten seconds.

caching is not going to help here. I can use the statement in 24358390 with a where clause to filter by table name, but for that
I will have to first send a request for the field list(DB request)
Process the Column Names and build a select statement(Processing at the App server)
send a request to the server again to get the data (DB request)

I want to achieve all the three in a single request to the server (DB Request). I am using SQL Server 2000/2005 and C#.


0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24361127
Dynamic tables, temp tables, global temp tables it gets more and more fun by the day. temp and global temp in the tempdb database have to be referred to using the object_id of the name because the real name (as in information schema) starts the same, but it's given a unique name - so, would need to make sure you are picking up the correct instance of that temp table (ie object_id)

But it doesn't change anything.

If you are a C# developer, create a CLR function - but then that (like a stored procedure) does need to be distributed / releases / installed site by site.

Have you got any thoughts as to how you tink you might like to proceed ?
0
 
LVL 12

Author Comment

by:udayakumarlm
ID: 24361148
there is a simple setup application which will install an application/service. this is my app server. the data base is of a third party product so backup restore of the same is not in my control and i can not create any SP/Views on the same.

CLR is also ruled out as there are variations of DB Versions  SQL 2000/2005/2008/EE etc. and deployment of CLR may not be possible due to DB rights issue in some sites.

my only option is to send a query to the DB server and get the data that i desire. i have to do this with a single execution request. I feel there is a way to do it.

I can wait for some more time as my app server needs some more time to fine tune for final deployment.

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24361255
>>I feel there is a way to do it<<  think that should be more like "hope" or "want" rather than "feel" unless you know something the experts here don't.

Danger now is that a lot of experts have given good advice and I am now getting the feeling that their collective efforts are going to be unrecognised because of your postion that accepting a "cannot be done" answer is not what you want, and therefore considered to be the wrong answer.

But anyway, we can sit and watch and see what happens. Have been exploring a few things, but it always comes back to one extra step.

access to the information_schema.columns does not require any specific privilege that I am aware of (other than normal access), and if there are temp tables being created, assume that the app must be involved in that process, so ideally are created without the instantiation of unwanted columns.

without touching the database, it is really the only way to first find the catalogue views / metdata / schema to pick the columns you want and then use that to build the query string. But it is a two step process. The connections are already establised, the information_schema views are tight and compact, they are compatable for extracting column names across the three versions (e.g. select column_name from information_schema.columns where table_name = 'my_target_table' and and left(column_name,1) <> '_'), so should not present any massive overhead, or load on the app in order to achieve what you need. You could do it once up the front, and cache the column names locally or in memory for those tables of specific interest - reducing the number of database trips to just 1. Then make sure temp tables are not created with unwanted columns, or, if a little more contricted in size, then resolve after returning the rowset.

0
 
LVL 12

Author Comment

by:udayakumarlm
ID: 24361352
thank you mark for the comments, the issue is with kind of load i am anticipating on the app server i don't want to cache any more memory than required. it is a multi threaded app. each thread will create a temp table and will retrieve the data and delete the table.

I do not under estimate the experts on EE as i am also one of them. I am certain that the task can be achieved with a single request to Database server. this will reduce the burden on the app server. there is no restriction on the performance imprecations as SQL will handle things better than my app when it comes to performance.

let us see if some one can solve this problem.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24361565
indeed you are - even a Guru in SQL 2005 !! then you do understand, which is a good thing.

One of the big challenges (other than the original question) is that some of the things that we can achieve in say 2008, is not going to happen in sql2000, so really the solution has to be at that lowest level / common denominator.

Sounds like an interesting app... Curiousity picks up after everyone of your postings :)

let's see what transpires...

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24361572
What drivers are you using to connect to the database ?
0
 
LVL 12

Author Comment

by:udayakumarlm
ID: 24361700
I am using System.Data.SQLClient  as provider and "Data Source=localhost;Initial Catalog=shoper9ho;User ID =sa;Password =admin" as connection string.

Not tested on older versions of SQL but working fine on 2000/2005/2008.

we have the functionality ready with two requests to the server. but when it comes to optimization this was one of the issues to be sorted out.

0
 
LVL 12

Author Comment

by:udayakumarlm
ID: 24362066
finally,

one of my colleague suggested to execute the queryies as batch as following
USE DBName DECLARE @Columns VARCHAR(5000) SELECT @Columns = ISNULL(@Columns + ',', '') + CAST(COLUMN_NAME AS VARCHAR(50))  FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = 'temp__SIS94db30890631' AND COLUMN_NAME NOT LIKE '__RowIDCheck' EXEC('SELECT '+ @Columns +' FROM temp__SIS94db30890631' + ' WHERE __RowIDCheck >= 0 AND __RowIDCheck <= 4')

this entire string is sent at once to the database and the result can be used in creation of dataset/datareader.

this will not require any processing at the application end as DBName ,temp__SIS94db30890631 are simply replaced with the actual values and sent to the server.


 
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24364132
that is not so different from riteshshah (apart from executesql rather than exec), you can always string together commands. Best to use semicolons between, what you are doing is a common sql injection trick.

but did not think that was what you were saying - I thought you wanted it in a single query meaning a single command.

ah well... there you go... slight misunderstanding leading
0
 
LVL 40

Expert Comment

by:Sharath
ID: 24370301

This is already posted by Ritesh. In you query also you are querying the database more than once. one for querying Information_Schema.columns and other for your actual data.
0
 
LVL 12

Author Comment

by:udayakumarlm
ID: 24370876
sharath, yes it might of helped my colleague to build to solution but it is not actual solution. we had this script for days before i posted the question. our requirement was to fit it into single request. only mistake i did is that i was referring to it as a single query. but in the subsequent comments i believe i did explained in detail my requirement.

the solution was actually how to fit it in to a single request from C# to the db server. thank you all for your help.
0
 
LVL 12

Author Closing Comment

by:udayakumarlm
ID: 31578338
might have helped my colleague to solve the problem. but not the actual answer as the question was specific of not using SP/View.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video discusses moving either the default database or any database to a new volume.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now