August 2, 2013 at 1:49 am
Hi,
Is there any catalog view for finding reference objects in other database or same but is written in dynamic query in view/store procedure/function.
I found some like syscomments,sys.all_sql_modules,sys.sql_modules but is there some catalog view like
sys.all_expression_dependencies which does not show objects referred in dynamic queries.
Please Suggest :-).
,Regards
Harsimranjeet Singh
August 2, 2013 at 7:30 am
harsimranjeetsinghwasson (8/2/2013)
Hi,Is there any catalog view for finding reference objects in other database or same but is written in dynamic query in view/store procedure/function.
I found some like syscomments,sys.all_sql_modules,sys.sql_modules but is there some catalog view like
sys.all_expression_dependencies which does not show objects referred in dynamic queries.
Please Suggest :-).
,Regards
Harsimranjeet Singh
I am not quite sure what you are looking for. Are you looking for references in things like stored procs where the references are in dynamic sql within those procs? Can you please try to clarify what you are looking for?
_______________________________________________________________
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/
August 2, 2013 at 9:54 am
AFAIK there's no view that would capture references in dynamic queries DIRECTLY;
you could look at cached execution plans, and parse the xml of the plan for server/database/schema/table; but that's only going to get you items with cache-able plan, and that are still in the current cache since the last restart.
the plans?
SELECT decp.query_plan
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS decp
WHERE decp.query_plan IS NOT NULL
at that point, i'd stick the xml in a text file and use Regulus expressions on it instead. playing with xml is a little obscure for me sometimes.
i have this saved in my snippets for example, where i'm getting missing index schema/table from the plans:
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
Lowell
August 2, 2013 at 12:04 pm
this query specifically gives me the server/database/table/column that was used int eh output list of the query.
maybe this gets you what you were looking for?
i had to create a linked server adhoc query to get a value in Server column to show up int he query plan
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
SELECT
decp.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/@StatementText)[1]', 'NVARCHAR(256)') ,
decp.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:RelOp/sp:OutputList/sp:ColumnReference/@Server)[1]' , 'varchar(100)') AS [Server] ,
decp.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:RelOp/sp:OutputList/sp:ColumnReference/@Database)[1]' , 'varchar(100)') AS [DATABASE] ,
decp.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:RelOp/sp:OutputList/sp:ColumnReference/@Table)[1]' , 'varchar(100)') AS
,
decp.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:RelOp/sp:OutputList/sp:ColumnReference/@Table)[1]' , 'varchar(100)') AS [COLUMN] ,
decp.query_plan
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS decp
WHERE decp.query_plan IS NOT NULL
Lowell
August 2, 2013 at 1:36 pm
Yes, i need the detail of objects referred from other database in a database
for example : departmentdw database has some procedure that refer amazon database objects like tables
USe departmentdw
create proc a
as
begin
exec 'select * from Amazom.dbo.people'
end
i want to find these database references which are mentioned in dynamic query. I can used other catalogs views
like syscomments and all writter in my question by i like to know some catalog view like sys.all_expression_dependencies which i found out doesn't show database object referred in dynamic queries
August 2, 2013 at 1:39 pm
Sure i'll try your both solutions.
Thanks 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply