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

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.Employee开发者_Python百科ID
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 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 .

---

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

---

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