table name and column name

Question

Post reply

table name and column name

rcola

Newbie

Points: 2
More actions
January 26, 2005 at 4:56 am

#60687

Hi to all,
executing this query "SELECT * FROM table1,table2 where ..."
can I have this kind of result in column description?
table1.column1,table1.column2,table2.column1,table2.column2
Thanks a lot.
Rico

Viewing 9 posts - 1 through 8 (of 8 total)

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

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 1

If you explicitly name the columns in the select clause you could use AS for each column, ie

SELECT TABLE1.COLUMN1 AS [TABLE1.COLUMN1], TABLE2.COLUMN2 AS [TABLE2.COLUMN2] FROM TABLE1, TABLE2....

Shamless self promotion - read my blog http://sirsql.net

rcola Newbie Points: 2 More actions · Answer 2

This is the first solution I've found out,

but I'm looking for an alternative way to do it

without writing all the columns name.

Rico

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 3

There's not one that I am aware of.

Shamless self promotion - read my blog http://sirsql.net

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

The only fast way to do this is to use the query builder of enterprise manager. If you have more than 1 table, it will automatically put the tablename name or alias in front of each field in the whole query.

PW-201837 SSC-Insane Points: 20805 More actions · Answer 5

Cursor over information_schema.columns for both tables, build the column list into a varchar and exec dynamix SQL with the resulting query.

Sam Shiell Ten Centuries Points: 1373 More actions · Answer 6

Rico said : " This is the first solution I've found out, but I'm looking for an alternative way to do it without writing all the columns name."

Why?

Sam

Sam Shiell Ten Centuries Points: 1373 More actions · Answer 7

Rico said : " This is the first solution I've found out, but I'm looking for an alternative way to do it without writing all the columns name."

Why?

Sam

Hariharan-202345 SSC Eights! Points: 821 More actions · Answer 8

Hi Sam,

You can achieve the same by using the below query:

DECLARE @col_list varchar(1000)

Select @col_list = ''

Select @col_list =@col_list+ ([TABLE_NAME]+'.'+[COLUMN_NAME])+',' from INFORMATION_SCHEMA.COLUMNS

where table_name IN ('Table1','Table2')

Select @col_list = LEFT(@COL_LIST,LEN(@COL_LIST)-1)

print @col_list

Select @col_list From Table1, Table2

Kindest Regards,

Hari