SQLTeam.com | Weblogs | Forums

Is it possible to add a prefix to all columns of a joining table?

Hello

Is there a quick way to add a prefix for the columns of the table that I join?

E.g.
FROM t1
LEFT JOIN t2 on col1=col2

I would like all columns from t2 to be renamed as 't2 - [original column name]' without having to list and rename each.

Thanks!

Dynamic query but you dont want to do that

Thanks, how with dynamic query and why I don't want that?

Generate a view with those columns names and join to the view.

Why do you want to do this? How is it going to help with your code - or the output? What would happen if you had to join to the same table - multiple times?

FROM t1
LEFT JOIN table2 t2 ON t2.key = t1.key
LEFT JOIN table2 t3 ON t3.key = t2.otherkey

Now you would be returning all the columns from 't3' as 't3_columnname'?

If this is to help with coding - then make sure you use the table alias when referencing the columns - always.

hi

This is a very very common scenario

My one idea is

My second idea is
Do sp_help on the table .. copy paste the columns from SSMS
into Notepad++ and search for begining of line and add prefix

My third idea is
use excel ..
put sp_help ssms column names in 2nd column
put column prefix in 1st column
put comma in 3rd column
Copy .. Paste

Thanks this looks very interesting but it will rename the columns in the actual table while I only want to rename the columns in the produced selection having only read-access.

Why?