How to Retrieve the columns...

Question

How to Retrieve the columns...

Bagath

SSCrazy

Points: 2409
More actions
April 20, 2006 at 3:50 am

#172172

Hi,

Here is my query ,
I have a table with the 100 columns.Now i want to display only 99 columns through the select statemnet.Can any one give me a query to this.

Thanks,
Gaddan.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

RyanRandall SSChampion Points: 13623 More actions · Answer 1

You have to list all the columns. You can just type them out, but I'm guessing it's that you want to avoid.

There are various ways to save your typing. Maybe the most straightforward is to right-click on the table in the object browser in query analyser and do 'script object to new window as -> select'.

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

venkat-216866 SSC Veteran Points: 257 More actions · Answer 2

set nocount on

Declare @TableName varchar(128), @ColumnList varchar(1000)

Declare @SQL varchar(1000)

set @TableName = 'your Table'

SELECT @ColumnList = COALESCE(@ColumnList + ', ', '') + column_name

FROM INFORMATION_SCHEMA.Columns

WHERE Ordinal_position <= 95

and table_name = @TableName

Set @SQL = 'Select ' + @ColumnList + ' from ' + @TableName

Bagath SSCrazy Points: 2409 More actions · Answer 3

Thanks for the reply...

But i did not explain my view clearly in that....

I want to display 99 columns with data.i think now u got wot my query is?

Thanks,

Bagath SSCrazy Points: 2409 More actions · Answer 4

For ex:-

I have a table with 3 columns as below

select * from emp

no name sal

2 Joe 2000.00

3 Mary 1000.00

4 Julie 5000.00

2 Joe 1000.00

1 Ram 1000.00

Now my question i want to display only two columns with data.and one more thing is here the columns are only two but i have to use this one as dynomic purpose,instead of writing all col names.

no name

2 Joe

3 Mary

4 Julie

2 Joe

1 Ram.

sushila SSC-Dedicated Points: 35293 More actions · Answer 5

Since you want to use this dynamically is it safe to assume that the table names will also be dynamic and that there is more than one table you'll be querying this way ?!?!

Also, when you say you want to return 99 columns out of 100 which is the one you want to eliminate ?! For example, in any given table, would it be the last column (ordinal position) that you don't want to include in your query ?!?!

**ASCII stupid question, get a stupid ANSI !!!**

Bagath SSCrazy Points: 2409 More actions · Answer 6

Thanks,

Ya You are right...that would be the last column that i dont want to include in my quuery....

Table name will not be dynomic,columns will be dynomic.In that i want to exclude the last col.

Thant means i want to select all the columns except the last column.Got it?

Regards,

Gaddan.

chisholmd SSCrazy Points: 2490 More actions · Answer 7

Just curios but why do you not want the last column? Is it a 'text' field? I ask because if it is a smallish field you could just retrieve it anyway even if your not going to use it in your app.

If it is a large column maybe you should split it off into its own table anyway. This would solve your immediate problem and probably give you better performance on other operations.

Dave

Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)

Bagath SSCrazy Points: 2409 More actions · Answer 8

It is an 'integer' field and i want to use this type of query in reports that would not display the last columns.

This is the one small task i have to do.Is there any easy way to do like that.

Thanks,

sushila SSC-Dedicated Points: 35293 More actions · Answer 9

It it's just one table then I can't understand why you don't just type out the column names...

At any rate, Venkat has already posted the solution for you - the only addition you would have to make is to "exec" the sql..

DECLARE @strColNames VarChar(500)
DECLARE @tblName VarChar(50)

SET @tblName = 'myTable'


SELECT @strColNames = ISNULL(@strColNames +', ', '') + name FROM syscolumns where id = object_id(@tblName)
AND colorder NOT IN (SELECT MAX(colorder) FROM syscolumns where id = object_id(@tblName))
PRINT @strColNames

EXEC('SELECT ' + @strColNames + ' FROM ' + @tblName)

**ASCII stupid question, get a stupid ANSI !!!**

sushila SSC-Dedicated Points: 35293 More actions · Answer 10

Just realized that Venkat's using information_schema views which is a much better option:

change your select to get your column names from this instead..

DECLARE @strColNames VarChar(500)
DECLARE @tblName VarChar(50)

SET @tblName = 'myTable'


SELECT @strColNames = ISNULL(@strColNames +', ', '') + COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tblName AND ORDINAL_POSITION < (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tblName)

--PRINT @strColNames

EXEC('SELECT ' + @strColNames + ' FROM ' + @tblName)

**ASCII stupid question, get a stupid ANSI !!!**

Bagath SSCrazy Points: 2409 More actions · Answer 11

Hey Thanks,

Thats what i want.That is only one table and the columns are dynomic.After creating the table i am adding one more column(generatedid) for sequence numbers but i dont want to display that column while retreiving the data.That is my requirement.

Thanks once again...

Regards,

Gaddan.