January 26, 2005 at 4:56 am
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
January 26, 2005 at 5:40 am
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....
January 26, 2005 at 7:06 am
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
January 26, 2005 at 8:24 am
There's not one that I am aware of.
January 26, 2005 at 8:46 am
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.
January 26, 2005 at 9:35 am
Cursor over information_schema.columns for both tables, build the column list into a varchar and exec dynamix SQL with the resulting query.
January 27, 2005 at 2:54 am
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
January 27, 2005 at 2:55 am
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
January 27, 2005 at 3:29 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply