Expanding SELECT * - 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 2005 Forums

Transact-SQL (2005)

Expanding SELECT *

Author

Topic

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2007-06-06 : 09:01:04

I'm working with some sub-queries, and have two tables joined, both tables have a ROWID field (and others) with the same name. Format like:

select * from xxx join (SELECT COL.*, CO.* from cust_order_line col join cust_order co on co.id = col.cust_order_id where co.id = 123) c on (c.yyy = xxx.bb)

I get the error: The column 'ROWID' was specified multiple times for 'c'. ..because COL.ROWID exists and CO.ROWID exists in the subquery, both tables have over 100 fields I want.. is there any way with management studio to dump all of the fields in, then I can eliminate the few I don't need from the many I do?
Thanks,

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-06 : 09:10:54

avoid using select * specify the column you want specifically.

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2007-06-06 : 09:19:46

quote:
Originally posted by khtan

avoid using select * specify the column you want specifically.

KH

I'd love to, but I don't have time to type the 100 columns out in every select statement.. I'm looking for a way to have the management studio type them out.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-06 : 09:29:57

well you have to... somehow
make use of the information_schema to list the column name out and copy and paste into your query.


select name from information_schema.tables where table_name = 'yourtable'

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-06 : 09:41:01

Or you can use Peso's trick to bring CSV list of columns like this:

SELECT DISTINCT	
		STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + s2.COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS s2 WHERE s2.TABLE_NAME = s1.TABLE_NAME ORDER BY ',' + s2.COLUMN_NAME FOR XML PATH('')), 1, 1, '') AS COLUMNS
FROM		INFORMATION_SCHEMA.COLUMNS AS s1
WHERE S1.TABLE_NAME = 'yourtable'

Note: The above code will work only for SQL Server 2005.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-06 : 09:43:43

quote:
Originally posted by khtan

well you have to... somehow
make use of the information_schema to list the column name out and copy and paste into your query.

select name from information_schema.tables where table_name = 'yourtable'

KH

Select column_name+',' from information_schema.columns
where table_name='your table'

Madhivanan

Failing to plan is Planning to fail

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-06-06 : 14:12:11

Assuming you are using Management Studio, you can use the Object Explorer. Right click on the table you want to get the columns from and select Script Table as.. / Select To.. / <take your pick>.

rudesyle
Posting Yak Master

110 Posts

Posted - 2007-06-06 : 14:26:48

quote:
Originally posted by Lamprey

Assuming you are using Management Studio, you can use the Object Explorer. Right click on the table you want to get the columns from and select Script Table as.. / Select To.. / <take your pick>.

Also, with tools like Visual Studio, there's a query builder built right into the editor. just tick the checkboxes next to each column name...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-06 : 21:24:29

With all the above, there is no excuse to use SELECT *

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-07 : 10:11:25

Use select * only when use use exists or not exists

If exists(select * from .......)
--do some stuff

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-06-07 : 11:59:45

"With all the above, there is no excuse to use SELECT *"

Yeah, but only once you know that they exist and how to use them

phdiwakar
Starting Member

15 Posts

Posted - 2007-06-07 : 12:12:48

Kbalz,
while you can do sql query from information_schema.columns to get the list of columns, there is also another way to do it IN Management Studio itself:

In the object Explorer, expand your table and select columns
Left click on columns and drag them to your Query Editor

You will get all your columns with comma separation.

X002548
Not Just a Number

15586 Posts

Posted - 2007-06-07 : 13:16:18

quote:
Originally posted by madhivanan
Select column_name+',' from information_schema.columns
where table_name='your table'

Nah

Select ' '+Column_name+',' from information_schema.columns
where table_name='your table' AND ORDINAL_POSITION = 1
UNION ALL
Select ', '+Column_name+',' from information_schema.columns
where table_name='your table' AND ORDINAL_POSITION <> 1
ORDER BY ORDINAL_POSITION

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2007-06-07 : 13:46:00

[code]
SELECT CASE WHEN ORDINAL_POSITION = 1 THEN ' ' ELSE ', ' END
+ COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable'
ORDER BY ORDINAL_POSITION
[/code]
Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-07 : 23:54:21

quote:
Originally posted by X002548

quote:
Originally posted by madhivanan
Select column_name+',' from information_schema.columns
where table_name='your table'

Nah

Select ' '+Column_name+',' from information_schema.columns
where table_name='your table' AND ORDINAL_POSITION = 1
UNION ALL
Select ', '+Column_name+',' from information_schema.columns
where table_name='your table' AND ORDINAL_POSITION <> 1
ORDER BY ORDINAL_POSITION

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Server: Msg 104, Level 16, State 1, Line 1
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-08 : 00:02:16

quote:

Originally posted by Kristen


SELECT	CASE WHEN ORDINAL_POSITION = 1 THEN ' ' ELSE ', ' END
	+ COLUMN_NAME
FROM	INFORMATION_SCHEMA.COLUMNS
WHERE	TABLE_NAME = 'MyTable'
ORDER BY ORDINAL_POSITION

Kristen

Do you need order by?
I think both information_schema.columns and sp_help will give the columns in order of ordinal_position by default

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-06-08 : 03:06:36

"Do you need order by?"

Need something to not get a comma before the first entry ...

... but personally I prefer to have the Columns in ordinal_position. We take some trouble to "group" columns that relate to each other to be adjacent in the schema - e.g. [Height] and [Width]

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources