Column extration - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Column extration

Author

Topic

gksharma
Starting Member

11 Posts

Posted - 2007-08-25 : 05:30:25

Hello,
There are 40 columns in a table, i want to fetch all the columns; but without only one particular column and without mentioning all the column names. How it is possible?
Please guide me...!
Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-25 : 05:41:01

Select column_name from information_schema.columns
where table_name='your_table' and column_name<>'unwanted_column'

Madhivanan

Failing to plan is Planning to fail

gksharma
Starting Member

11 Posts

Posted - 2007-08-25 : 05:43:50

Yes, it works but at the same time data from that table data should also be get.

Thanks & Regards

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-25 : 07:54:29

You need to modify this accordingly

declare @sql varchar(2000)
select @sql=''
Select @sql=@sql+case when ordinal_position=1 then '' else ',' end +column_name from information_schema.columns
where table_name='your_table' and column_name<>'unwanted_column'
order by ordinal_position
select 'select '+@sql+' from your_table'

Madhivanan

Failing to plan is Planning to fail

gksharma
Starting Member

11 Posts

Posted - 2007-08-26 : 01:26:39

Till Now what i have done :
declare @sql varchar(2000)
select @sql=''
Select @sql=@sql+case when ordinal_position=1 then '' else ',' end +'['+column_name+']' from information_schema.columns
where table_name='COMTR' and column_name<>'GROUP'
order by ordinal_position
SELECT @SQL='SELECT '+@SQL+' FROM COMTR'
--EXEC @SQL
--PRINT @SQL
It is not executing the sql string; but if i print the string it shows the complete sql query as well.
So, how can i execute this string...!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-27 : 02:09:29

Uncomment --EXEC @SQL

Madhivanan

Failing to plan is Planning to fail

gksharma
Starting Member

11 Posts

Posted - 2007-08-27 : 02:36:52

Oh.. i simply forget to remove comment here only...!
but in the query analyser it shows error while executing the string
error is [Microsoft][ODBC SQL Server Driver]Syntax error or access violation.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-27 : 03:56:21

What do you get when printing @SQL?

Madhivanan

Failing to plan is Planning to fail

gksharma
Starting Member

11 Posts

Posted - 2007-08-27 : 04:35:53

Two problems :
1. if i used a table which is having around 40-50 columns then it is running with EXEC (@SQL) fine, but sometime shows error.
2. if i used a table which is having around 160-170 columns then in the sql string it shows something like "select ,[myfield1],[myfield2],..... from mytalble"
so, i think "," after select is making error. But it is not coming in small tables...!
So, very confused how it is going on...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-27 : 05:27:28

Use

declare @sql varchar(8000)

instead of

declare @sql varchar(2000)

Madhivanan

Failing to plan is Planning to fail

gksharma
Starting Member

11 Posts

Posted - 2007-08-27 : 05:44:54

Thank You Madhivanan....!
Finally i got the results with following query...
declare @sql varchar(8000)
select @sql=''
Select @sql=@sql+case when ordinal_position=1 then '' else ',' end +'['+column_name+']' from information_schema.columns
where table_name='comtr' and column_name<>'roll_no'
order by ordinal_position
if(LEFT(@SQL,1)=',') select @SQL=STUFF(@SQL,1,1,'')
SELECT @SQL='SELECT '+@SQL+' FROM comtr'
--print @sql
EXEC (@SQL)

Thanks again and warm Regards...!
Girish Kumar Sharma

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-27 : 06:16:26

This will work even when you take the dive to SQL Server 2005.
And you are safe for "illegal" characters in the column name such as ' [ ] which will mess up your code

DECLARE	@SQL VARCHAR(8000)

SET @SQL = 'SELECT '

SELECT	@SQL = @SQL + ',' + d.ColName
FROM	(
		SELECT		TOP 100 PERCENT
				QUOTENAME(Column_Name) AS ColName
		FROM		INFORMATION_SCHEMA.COLUMNS
		WHERE		Table_Name = 'comtr'
				AND Column_Name <> 'roll_no'
		ORDER BY	Ordinal_Position
	) AS d

SET	@SQL = STUFF(@SQL, 8, 1, '') + ' FROM comtr'

--PRINT @SQL
EXEC (@SQL)