November 28, 2011 at 10:50 am
Good morning everyone.
I'm currently trying to when given a query in an NVARCHAR to determine through T-SQL which objects (tables, columns, sproc, functions, etc) the SQL statement touches.
I'm hoping that there is an easy method to pull out programatically (T-SQL) all this information - almost like pulling up the query plan. I've tried several searches and tried looking into SHOWPLAN_XML.
Has anyone had to do something similar to this and can provide a few pointers? Without knowing how to word what I'm after I've been unable to gather anything from searches. Thanks!
November 28, 2011 at 10:57 am
razmage (11/28/2011)
Good morning everyone.I'm currently trying to when given a query in an NVARCHAR to determine through T-SQL which objects (tables, columns, sproc, functions, etc) the SQL statement touches.
I'm hoping that there is an easy method to pull out programatically (T-SQL) all this information - almost like pulling up the query plan. I've tried several searches and tried looking into SHOWPLAN_XML.
Has anyone had to do something similar to this and can provide a few pointers? Without knowing how to word what I'm after I've been unable to gather anything from searches. Thanks!
Are you trying to write a function or something to receive a nvarchar parameter and have it return all objects affected by that query? That seems to be logistically impossible. Perhaps if you can more clearly explain what you are trying to accomplish we can help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 28, 2011 at 11:00 am
i'm not an expert in xml(yet), but i fiddled with this before:
I'm assuming you mean you have an NVARCHAR dynamic SQL thing going on?
this is getting all the plans in the cache, and if the plan had a StmtSimple in the plan, I chopped it up to get the tables and columns..
so for example, SELECT * FROM SOMEVIEW ends up getting me the base tables and columns.
i don't know if this works on procedures fiddling with tables, but it might get you started down the rabbit hole.
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
SELECT p.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/@Database)[1]', 'NVARCHAR(256)') AS DatabaseName
,s.sql_handle
,s.total_elapsed_time
,s.last_execution_time
,s.execution_count
,s.total_logical_writes
,s.total_logical_reads
,s.min_elapsed_time
,s.max_elapsed_time
,p.query_plan
,p.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/@Table)[1]', 'NVARCHAR(256)') AS TableName
,p.query_plan.value(N'(/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/@Schema)[1]', 'NVARCHAR(256)') AS SchemaName
,p.query_plan.value(N'(/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/@Impact)[1]', 'DECIMAL(6,4)') AS ProjectedImpact
,ColumnGroup.value('./@Usage', 'NVARCHAR(256)') AS ColumnGroupUsage
,ColumnGroupColumn.value('./@Name', 'NVARCHAR(256)') AS ColumnName
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) AS p
CROSS APPLY p.query_plan.nodes('/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/sp:ColumnGroup') AS t1 (ColumnGroup)
CROSS APPLY t1.ColumnGroup.nodes('./sp:Column') AS t2 (ColumnGroupColumn)
WHERE p.query_plan.exist(N'/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan//sp:MissingIndexes') = 1
ORDER BY s.total_elapsed_time DESC
-edit: this is using missing indexes, i have another example somewhere that is getting the Reop OutputList, which is really what you are after-
Lowell
November 28, 2011 at 11:54 am
Thanks for the replies.
@Lowell, I'll check down that route and see what that gives me. I think that may be a good start.
@Sean, I have a lot of SQL queries that are fairly long with a lot of joins, etc in them. I've been asked with making a list of all the tables and columns that each query is referencing so that we can do some cross-examination. These are all queries that are then used in reports. I'm looking for a way to be able to pull this data out of the SQL in a repeatable way so we can automate this task as much as possible instead of making it a manual process to document and re-document as queries are changed or added.
November 28, 2011 at 12:03 pm
So these are queries and not stored procedures? If they're stored procs you can use the dependency DMOs, sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 28, 2011 at 2:14 pm
Grant Fritchey (11/28/2011)
So these are queries and not stored procedures? If they're stored procs you can use the dependency DMOs, sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities.
Hi Grant. No these are queries and not sprocs. I will store that tid-bit away though for when this question comes up for sprocs though 🙂
November 28, 2011 at 2:52 pm
razmage (11/28/2011)
Grant Fritchey (11/28/2011)
So these are queries and not stored procedures? If they're stored procs you can use the dependency DMOs, sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities.Hi Grant. No these are queries and not sprocs. I will store that tid-bit away though for when this question comes up for sprocs though 🙂
Phew!!! That is going to be tough. There are just so many ways that something could fall through the cracks. Maybe Lowell will be able to scare something up (he has some sort of magical script vault that can summon up some unbelievable stuff).
This might be a really good argument for converting this stuff to sprocs instead of pass through queries or dynamic sql.
This is especially challenging because typically documentation is built the other way around. In other words, you take an object and determine what stuff needs it. You are trying to determine which objects are depended on by dynamic sql. That could prove nearly impossible.
How do anticipate handling functions? For example you have a couple UDFs in your dynamic sql string, do you need to know all the details for the UDF in addtion?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply