Databases
StackOverflow2013
Postsdbo
PO
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    text
    copied!<p>To do this kind of analysis you need a full blown (T-)SQL parser, which has access to the current structure of the database (hint: the column expressions can get arbitrarily complex, with subselects and everything).</p> <p>UPDATE: My idea below with the query plan does not work. While the plan nicely traces the column references it does not show the output columns in the correct order. ARGH! I can't see at the moment how this could be healed, there is no ordinal number or so in the attributes. So, sorry, doesn't work. I still leave it in for reference, maybe someone else has an idea based on this.</p> <hr> <p>One thing that comes to mind is looking at the query plan. You would have to parse the XML plan to see where the value for a certain column originates. While this may be simpler to do than looking directly at the SQL it's still not trivial at all. Query plans can get quite complex, too. But to illustrate the principle let's look at a similar query on the AdventureWorks DB:</p> <pre><code>SELECT rowguid, Left(JobTitle,3), 'hardcoded string', BirthDate, Replace(BirthDate,'@xyz.com','') FROM HumanResources.Employee </code></pre> <p>The XML query plan for this statement looks like this:</p> <pre><code>&lt;?xml version="1.0" encoding="utf-16"?&gt; &lt;ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="11.0.3128.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"&gt; &lt;BatchSequence&gt; &lt;Batch&gt; &lt;Statements&gt; &lt;StmtSimple StatementCompId="1" StatementEstRows="290" StatementId="1" StatementOptmLevel="TRIVIAL" StatementSubTreeCost="0.00807444" StatementText="Select rowguid, Left(JobTitle,3),'hardcoded string',BirthDate,Replace(BirthDate,'@xyz.com','') FROM HumanResources.Employee" StatementType="SELECT" QueryHash="0x5F035E11344539B" QueryPlanHash="0x717B3D06C26C61ED" RetrievedFromCache="false"&gt; &lt;StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" /&gt; &lt;QueryPlan CachedPlanSize="16" CompileTime="14" CompileCPU="12" CompileMemory="152"&gt; &lt;MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" /&gt; &lt;OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="418321" EstimatedPagesCached="104580" EstimatedAvailableDegreeOfParallelism="2" /&gt; &lt;RelOp AvgRowSize="4045" EstimateCPU="2.9E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="290" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00807444"&gt; &lt;OutputList&gt; &lt;ColumnReference Database="[AdventureWorks2012]" Schema="[HumanResources]" Table="[Employee]" Column="BirthDate" /&gt; &lt;ColumnReference Database="[AdventureWorks2012]" Schema="[HumanResources]" Table="[Employee]" Column="rowguid" /&gt; &lt;ColumnReference Column="Expr1003" /&gt; &lt;ColumnReference Column="Expr1004" /&gt; &lt;ColumnReference Column="Expr1005" /&gt; &lt;/OutputList&gt; &lt;ComputeScalar&gt; &lt;DefinedValues&gt; &lt;DefinedValue&gt; &lt;ColumnReference Column="Expr1003" /&gt; &lt;ScalarOperator ScalarString="substring([AdventureWorks2012].[HumanResources].[Employee].[JobTitle],(1),(3))"&gt; &lt;Intrinsic FunctionName="substring"&gt; &lt;ScalarOperator&gt; &lt;Identifier&gt; &lt;ColumnReference Database="[AdventureWorks2012]" Schema="[HumanResources]" Table="[Employee]" Column="JobTitle" /&gt; &lt;/Identifier&gt; &lt;/ScalarOperator&gt; &lt;ScalarOperator&gt; &lt;Const ConstValue="(1)" /&gt; &lt;/ScalarOperator&gt; &lt;ScalarOperator&gt; &lt;Const ConstValue="(3)" /&gt; &lt;/ScalarOperator&gt; &lt;/Intrinsic&gt; &lt;/ScalarOperator&gt; &lt;/DefinedValue&gt; &lt;DefinedValue&gt; &lt;ColumnReference Column="Expr1004" /&gt; &lt;ScalarOperator ScalarString="'hardcoded string'"&gt; &lt;Const ConstValue="'hardcoded string'" /&gt; &lt;/ScalarOperator&gt; &lt;/DefinedValue&gt; &lt;DefinedValue&gt; &lt;ColumnReference Column="Expr1005" /&gt; &lt;ScalarOperator ScalarString="replace(CONVERT_IMPLICIT(varchar(40),[AdventureWorks2012].[HumanResources].[Employee].[BirthDate],121),'@xyz.com','')"&gt; &lt;Intrinsic FunctionName="replace"&gt; &lt;ScalarOperator&gt; &lt;Convert DataType="varchar" Length="40" Style="121" Implicit="true"&gt; &lt;ScalarOperator&gt; &lt;Identifier&gt; &lt;ColumnReference Database="[AdventureWorks2012]" Schema="[HumanResources]" Table="[Employee]" Column="BirthDate" /&gt; &lt;/Identifier&gt; &lt;/ScalarOperator&gt; &lt;/Convert&gt; &lt;/ScalarOperator&gt; &lt;ScalarOperator&gt; &lt;Const ConstValue="'@xyz.com'" /&gt; &lt;/ScalarOperator&gt; &lt;ScalarOperator&gt; &lt;Const ConstValue="''" /&gt; &lt;/ScalarOperator&gt; &lt;/Intrinsic&gt; &lt;/ScalarOperator&gt; &lt;/DefinedValue&gt; &lt;/DefinedValues&gt; &lt;RelOp AvgRowSize="80" EstimateCPU="0.000476" EstimateIO="0.00756944" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="290" LogicalOp="Clustered Index Scan" NodeId="1" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.00804544" TableCardinality="290"&gt; &lt;OutputList&gt; &lt;ColumnReference Database="[AdventureWorks2012]" Schema="[HumanResources]" Table="[Employee]" Column="JobTitle" /&gt; &lt;ColumnReference Database="[AdventureWorks2012]" Schema="[HumanResources]" Table="[Employee]" Column="BirthDate" /&gt; &lt;ColumnReference Database="[AdventureWorks2012]" Schema="[HumanResources]" Table="[Employee]" Column="rowguid" /&gt; &lt;/OutputList&gt; &lt;IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false"&gt; &lt;DefinedValues&gt; &lt;DefinedValue&gt; &lt;ColumnReference Database="[AdventureWorks2012]" Schema="[HumanResources]" Table="[Employee]" Column="JobTitle" /&gt; &lt;/DefinedValue&gt; &lt;DefinedValue&gt; &lt;ColumnReference Database="[AdventureWorks2012]" Schema="[HumanResources]" Table="[Employee]" Column="BirthDate" /&gt; &lt;/DefinedValue&gt; &lt;DefinedValue&gt; &lt;ColumnReference Database="[AdventureWorks2012]" Schema="[HumanResources]" Table="[Employee]" Column="rowguid" /&gt; &lt;/DefinedValue&gt; &lt;/DefinedValues&gt; &lt;Object Database="[AdventureWorks2012]" Schema="[HumanResources]" Table="[Employee]" Index="[PK_Employee_BusinessEntityID]" IndexKind="Clustered" /&gt; &lt;/IndexScan&gt; &lt;/RelOp&gt; &lt;/ComputeScalar&gt; &lt;/RelOp&gt; &lt;/QueryPlan&gt; &lt;/StmtSimple&gt; &lt;/Statements&gt; &lt;/Batch&gt; &lt;/BatchSequence&gt; &lt;/ShowPlanXML&gt; </code></pre> <p>Here, the SQL Server has already done all the hard work of parsing the SQL and determining where the column values eventually come from (and then optimizing the plan, but that's of no interest here).</p> <p>The first <code>&lt;OutputList&gt;</code> corresponds to the list of columns in your SELECT. It has 5 column references. Two correspond directly to table columns as you can see from their attributes, while the other three just reference some arbitrarily named expressions. Now you can hunt down these expressions (recursively if need be) which in turn have other column references (or not, in case of "Expr1004" which just references a constant).</p> <p>So, since you only need to look at a few elements of the plan, you have a good chance of hunting down the source columns (be aware that there may be more than one source column for a given output column).</p> <p>To get the XML query plan just execute</p> <pre><code>SET SHOWPLAN_XML ON &lt;your statement&gt; SET SHOWPLAN_XML OFF </code></pre> <p>The output is the plan (without executing the statement - it's just the estimated plan but that's of no importance here).</p> <p>Hope that helps.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload