How to get table name back in column name of joined tables

Question

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?

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

Kevin Holditch · Answer 1 · 2011-10-17T18:41:27.250

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 .

score 1 · Answer 2 · answered Oct 17 '11 at 10:00

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%'

Bogdan Sahlean · Answer 3 · 2011-10-17T10:33:03.193

You may try using the next method but I warn you: it's not precise.

To display column list (column name, table name, table alias, database name), after at least one execution, this solution uses cached plans. The next example can be run in AdventureWorks2008.

--First step is to run query
SET ANSI_WARNINGS ON;
GO
--QID:579F1EB7-3E68-4ED6-AED0-22E1890AF6CF
SELECT  TOP(10)
        h.SalesOrderID
        ,h.OrderDate
        ,e.JobTitle
        ,p.*
        ,p.FirstName + p.LastName --calculated field
FROM    Sales.SalesOrderHeader h
INNER JOIN HumanResources.Employee e ON e.BusinessEntityID = h.SalesPersonID
INNER JOIN Person.Person p ON e.BusinessEntityID = p.BusinessEntityID 
GO

--Second step
DECLARE  @plan_handle VARBINARY(64)
        ,@x XML;

SELECT  TOP(1)
        @plan_handle = qs.plan_handle
FROM    sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) txt
WHERE   txt.text LIKE '--QID:579F1EB7-3E68-4ED6-AED0-22E1890AF6CF%'
AND     txt.text NOT LIKE '%dm_exec_query_stats%'
--The last used plan (be careful with concurrent executions)
--Also, for the same query you may have many more plans (with parallelism or without)
ORDER BY qs.last_execution_time DESC;   

SELECT  @x = f.query_plan
FROM    sys.dm_exec_query_plan(@plan_handle) f;

WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT   a.b.value('@Database','NVARCHAR(128)') [Database]
        ,a.b.value('@Schema','NVARCHAR(128)')   [Schema]
        ,a.b.value('@Table','NVARCHAR(128)')    [Table]
        ,a.b.value('@Alias','NVARCHAR(128)')    [Alias]
        ,a.b.value('@Column','NVARCHAR(128)')   [Column]
FROM    @x.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/RelOp/OutputList/ColumnReference') a(b);

At the beginning of target query I used this comment --QID:guid (Query ID) for a more precise identification. If you have stored procedures, instead of sys.dm_exec_query_stats view can be used sys.dm_exec_procedure_stats (I have no tested) view to filter on database_id and object_id (stored procedure id).

Results:

Database               Schema             Table              Alias Column               
---------------------- ------------------ ------------------ ----- ---------------------
[AdventureWorks2008]   [Sales]            [SalesOrderHeader] [h]   SalesOrderID
[AdventureWorks2008]   [Sales]            [SalesOrderHeader] [h]   OrderDate
[AdventureWorks2008]   [HumanResources]   [Employee]         [e]   JobTitle
[AdventureWorks2008]   [Person]           [Person]           [p]   BusinessEntityID
[AdventureWorks2008]   [Person]           [Person]           [p]   PersonType
[AdventureWorks2008]   [Person]           [Person]           [p]   NameStyle
[AdventureWorks2008]   [Person]           [Person]           [p]   Title
[AdventureWorks2008]   [Person]           [Person]           [p]   FirstName
[AdventureWorks2008]   [Person]           [Person]           [p]   MiddleName
[AdventureWorks2008]   [Person]           [Person]           [p]   LastName
[AdventureWorks2008]   [Person]           [Person]           [p]   Suffix
[AdventureWorks2008]   [Person]           [Person]           [p]   EmailPromotion
[AdventureWorks2008]   [Person]           [Person]           [p]   AdditionalContactInfo
[AdventureWorks2008]   [Person]           [Person]           [p]   Demographics
[AdventureWorks2008]   [Person]           [Person]           [p]   rowguid
[AdventureWorks2008]   [Person]           [Person]           [p]   ModifiedDate
NULL                   NULL               NULL               NULL  Expr1006

How to get table name back in column name of joined tables

3 Answers3