Append Table Name to Field Name with Select *

Question

Sorry if this is a duplicate. I have searched but only find aliasing fields and tables.

I have a query:

 SELECT *
FROM MyTable1 ca LEFT OUTER JOIN MyTable2 dcn ON dcn.dstrct_code = ca.dstrct_code
                       LEFT OUTER JOIN MyTable2 cdn ON cdn.dstrct_code = ca.cost_dstrct_cde
                       LEFT OUTER JOIN MyTable3 bb ON bb.supplier_code = ca.supplier_code
 WHERE ca.dstrct_code = '0001'                                 
 AND ca.req_232_type = 'P'
 AND ca.requisition_no = '264982  000'
 AND ca.alloc_count = '01'
ORDER BY ca.alloc_count ASC

Please dont shoot me down for using * im not done with the query yet. If I execute this query I get a row of data however the tables I am selecting from all have a good number of fields and many are simularly named. So my question is... Is there anyway to select * from and append the table name to the field name so it is more obvious which field belongs to which table?

Answer 1

I don't think there's a way to do that directly but you can do this instead. Run a query like this:

SELECT 
    (case t.name when 'MyTable1' then 'ca' when 'MyTable2' then 'dcn' when 'MyTable3' then 'cdn' when 'MyTable4' then 'bb' end)
    + '.' + c.name
    + ' AS "' + t.name + '.' + c.name + '",'
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE t.name in ('MyTable1', 'MyTable2', 'MyTable3', 'MyTable4')
ORDER BY t.name

Run it, preferably with results to Text (Ctrl+T), and use the results instead of the * in your original query. You have to manually remove the comma from the last line.

If you like the approach, you could streamline the process with some dynamic SQL.