select * from tableName except Field names starting with _

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

Select allOpen in new window

which filed you wand to check for a _ ? if its name try below


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

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'

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

Ritesh, the question states "I can not use SP/Views."  So our Mission Impossible is to do what you are saying without the SP. :-)

:)

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.

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.

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.

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.

As long as single statement query concern, it can't be possible but yes, you can use SP which seems painful to you.

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.

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','_'

Select allOpen in new window

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.

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.

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

Select allOpen in new window

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.

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....

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.

are u doing this using any application...? what are ur limitations...? is it possible u to guess the Db Load ur talking about...?

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.

>>  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.

this will affect the performance of the application due to the validations need to be done to check for _

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.

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.

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 

Select allOpen in new window

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#.

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 ?

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.

>>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.

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.

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...

What drivers are you using to connect to the database ?

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.

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.


 

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

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.

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.

might have helped my colleague to solve the problem. but not the actual answer as the question was specific of not using SP/View.