SQLTeam.com | Weblogs | Forums

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

#1

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!

#2

Dynamic query but you dont want to do that

#3

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

#4

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

#5

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.

#6

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

image
image894×681 58.7 KB

#7
#8

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.

#9

Why?