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

1 Like
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?

11

Hi

Hope this helps

create sample data

drop table Temp1
drop table Temp2

create table Temp1 ( id int , name varchar(20) ,age int , address varchar(100))
insert into Temp1 select 1, 'Har',34,'Oak Street'
insert into Temp1 select 2, 'Car',23,'Ten Twelve'

create table Temp2 ( id int , name varchar(20) )
insert into Temp2 select 1, 'Har'
insert into Temp2 select 2, 'Car'

Query using sys.columns to get the columns for the table you want

SELECT 
   ',b.'+c.[name]+' AS ['+'Temp1 -'+ c.[name] +']'
FROM 
   sys.columns c 
WHERE 
   object_id = object_id('dbo.Temp1');

image

SELECT 
  a.*
,b.id AS [Temp1 -id]
,b.name AS [Temp1 -name]
,b.age AS [Temp1 -age]
,b.address AS [Temp1 -address]
FROM 
  Temp2 a 
     JOIN
  Temp1 b 
     ON a.id = b.id

image

1 Like