0

I hve the query...

select * from Contact c
left join Employee e on c.ContactID=e.ContactID
left join [Role] r on e.EmployeeID=r.EmployeeID
where FirstName like '%pete%'

It returns information where it finds it fine. However I would like to be able to see the tables that each column comes from without having to explicitely pick out each column and do a 'as' statement on the column name. Is this possible?

Exitos
  • 29,230
  • 38
  • 123
  • 178
  • No it is not possible. You could make a Mickey Mouse solution with a complex dynamic build sql. But I can't recommend that. I surgest you find a different solution. – t-clausen.dk Oct 17 '11 at 08:09
  • The information about table names is returned to the client. This question shows what it looks like in ADO. http://stackoverflow.com/questions/5442334/sql-server-stored-procedure/5450065#5450065 I'm sure there are ways of extracting the same info in other dev environments. BTW, using `select *` is a bad thing. http://stackoverflow.com/questions/262450/why-is-using-to-build-a-view-bad – Mikael Eriksson Oct 17 '11 at 08:11

2 Answers2

3

No I dont think it is possible. In general it is a bad idea to use "select *" in queries anyway so you'd be better of typing out the column names, this can be sped up using tools like management studio.

If you use select * this is bad because

1) if someone changes a table then the query will still work but it'll be harder to trace why you're not reading the data any more.

2) you could potentially be bringing more data back than you actually need, wasting read time and network bandwidth. It's generally good practice to just include necessary columns and then add more if you find you need them .

Kevin Holditch
  • 5,165
  • 3
  • 19
  • 35
1

This might help you. All columns of e are in front of the separator and all columns of r are after.

select e.*, '' as [_______], r.* from Contact c
left join Employee e on c.ContactID=e.ContactID
left join [Role] r on e.EmployeeID=r.EmployeeID
where FirstName like '%pete%'
Johan
  • 1,152
  • 7
  • 16