Post date: Nov 5, 2012 9:42:19 AM
I wanted to see which of our columns was being used for filtering, joining and output. I also needed to know which stored procedure that was using those columns.
What I did was to run all the stored procedures, capture their query plans in the aaPlans table. Then I was going to query those plans to get the information i needed.
Query the Query plan XML to get out the Output columns, Filter Columns and join Columns.
Ran into a dead end when i realized that aliased columns was aliased from other aliases.
DECLARE @xml XML;SELECT @xml = queryplan FROM aaPlans WHERE id = 58;--SELECT * FROM dbo.aaPlans where id = 171 WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')SELECT DISTINCT t.c.value('@Column', 'varchar(128)') AS FilterColumn ,t.c.value('@Table', 'varchar(128)') AS FilterTable , scalarString.s AS Expression FROM @xml.nodes('//Predicate//Identifier/ColumnReference') AS T(c)OUTER APPLY( -- take out the Expression if the filterTable is missing SELECT scalarString.s.value('@ScalarString', 'varchar(max)') AS Expression, alias.col.value('@Column', 'varchar(128)') AS ColumnAlias, actual.col.value('@Table', 'varchar(128)') AS AliasTable FROM @xml.nodes('//*[local-name() = "SequenceProject" or local-name() = "ComputeScalar"]/DefinedValues/DefinedValue') level1(col) OUTER APPLY level1.col.nodes('ColumnReference') ALIAS(col) OUTER APPLY level1.col.nodes('.//Identifier/ColumnReference') actual(col) OUTER APPLY level1.col.nodes('.//ScalarOperator') scalarString(s) WHERE t.c.value('@Table', 'varchar(128)') IS NULL AND t.c.value('@Column', 'varchar(128)') = alias.col.value('@Column', 'varchar(128)') ) scalarString(s, ColumnAlias, AliasTable); WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')SELECT DISTINCT t.c.value('@Column', 'varchar(128)') AS JoinColumn ,t.c.value('@Table', 'varchar(128)') AS JoinTable FROM @xml.nodes('//IndexScan/SeekPredicates//ColumnReference') AS T(c); WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')SELECT DISTINCT outputs.col.value('@Column', 'varchar(128)') AS OutputColumn, outputs.col.value('@Table', 'varchar(128)') AS OutputTable ,scalarString.s AS Expression FROM @xml.nodes('//StmtSimple/QueryPlan/RelOp/OutputList/ColumnReference') outputs(col)OUTER APPLY( -- take out the Expression if the filterTable is missing SELECT scalarString.s.value('@ScalarString', 'varchar(max)') AS Expression, alias.col.value('@Column', 'varchar(128)') AS ColumnAlias, actual.col.value('@Table', 'varchar(128)') AS AliasTable FROM @xml.nodes('//*[local-name() = "SequenceProject" or local-name() = "ComputeScalar"]/DefinedValues/DefinedValue') level1(col) OUTER APPLY level1.col.nodes('ColumnReference') ALIAS(col) OUTER APPLY level1.col.nodes('.//Identifier/ColumnReference') actual(col) OUTER APPLY level1.col.nodes('.//ScalarOperator') scalarString(s) WHERE outputs.col.value('@Table', 'varchar(128)') IS NULL AND outputs.col.value('@Column', 'varchar(128)') = alias.col.value('@Column', 'varchar(128)') AND scalarString.s.value('@ScalarString', 'varchar(max)') IS NOT NULL ) scalarString(s, ColumnAlias, AliasTable) ; --WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')--select distinct-- alias.col.value('@Column', 'varchar(128)') AS Alias,-- actual.col.value('@Column', 'varchar(128)') AS AliasFromColumn,-- actual.col.value('@Table', 'varchar(128)') AS AliasTable,-- scalarString.s.value('@ScalarString', 'varchar(max)') AS Expression--from -- @xml.nodes('//*[local-name() = "SequenceProject" or local-name() = "ComputeScalar"]/DefinedValues/DefinedValue') level1(col)--outer apply level1.col.nodes('ColumnReference') alias(col)--outer apply level1.col.nodes('.//Identifier/ColumnReference') actual(col)--outer apply level1.col.nodes('.//ScalarOperator') scalarString(s)--where scalarString.s.value('@ScalarString', 'varchar(max)') is not null